1

I wish to ensure I am using the "best" pattern when using an Execute As/Revert from within a Try/Catch block on SQL Server 2012. The below code "seems" to behave correctly... Am I missing anything or are there any security concerns, "better" approaches, etc.?

Below is my code:

CREATE PROCEDURE [dbo].[TryItOut]
WITH EXECUTE as 'NoTable1Access' --does not have access (select) to Table1!
AS
Begin
  Declare @execErrorNumber int,
           @execErrorMessage nvarchar(2048),
           @xactState smallint

  Begin Try
    Execute as user='HasTable1Access'
    select *, 1/0 as [SimulateError] from [T1].[Table1]; -- This will be a Stored Procedure call, but a select statement was easier to 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 some error processing in context of 'NoTable1Access'
   End Catch 

   select * from [T1].[Table1] --Should NOT have any access now and select should fail...
End
miah
  • 10,093
  • 3
  • 21
  • 32
RAA
  • 31
  • 5
  • It seems that you don't have a specific question or problem, but rather you're asking for a code review. If so, you may get a better response on the [code review site](http://codereview.stackexchange.com/). – Pondlife May 02 '13 at 19:35
  • Thanks @Pondlife, I did not know about the code review site and moved [my question](http://codereview.stackexchange.com/questions/25751/sql-server-execute-as-revert-pattern-in-a-try-catch-block) there... – RAA May 02 '13 at 19:49

0 Answers0