Tell me more ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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
share|improve this question
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. – Pondlife May 6 at 21:38
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! – RAA May 9 at 16:02

1 Answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.