SQL Server 'Execute As'/Revert pattern in a 'Try/Catch' Block -


i wish ensure using "best" pattern when using execute as/revert within try/catch block on sql server 2012. below code "seems" behave correctly... missing or there security concerns, "better" approaches, etc.?

below code:

create procedure [dbo].[tryitout] execute 'notable1access' --does not have access (select) table1! begin   declare @execerrornumber int,            @execerrormessage nvarchar(2048),            @xactstate smallint    begin try     execute user='hastable1access'     select *, 1/0 [simulateerror] [t1].[table1]; -- stored procedure call, select statement easier demo...     revert --revert on 'hastable1access'   end try   begin catch;          select @execerrornumber = error_number(),                @execerrormessage = error_message(),                @xactstate = xact_state();          revert -- revert on 'hastable1access' when in error...         --do error processing in context of 'notable1access'    end catch      select * [t1].[table1] --should not have access , select should fail... end 


Comments

Popular posts from this blog

linux - Does gcc have any options to add version info in ELF binary file? -

android - send complex objects as post php java -

charts - What graph/dashboard product is facebook using in Dashboard: PUE & WUE -