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

Question

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

• Pondlife: I realize that your code is an artificial example, but I don't immediately see what problem you're trying to solve. It looks like you want procedure 1 to call procedure 2, which should execute as a different user. But defining procedure 2 using WITH EXECUTE AS would do that without any need for impersonation. Perhaps if you can give some background about your actual purpose here, someone may be able to give a better response.
• RAA: What I am looking for is there a "hole" where user 'HasTableAccess' is NOT reverted ('HasTableAccess' context is still enforce for the remaining code base within the Stored Procedure... I believe that no matter what happens 'HasTableAccess' is always reverted!

Answer 1

One thing to be wary of is if you grant execute permissions on a schema in sql server, then no matter what's in the Stored Procedure, the user is granted rights to execute that stored procedure and whatever it entails.

So I think that, even if you change user contexts, that SP will work no matter what.