Use Stored Procedure to "EXECUTE AS USER" and Apply to Calling Context (Session)

impersonation, sql-server-2008-r2

Solution

It is not possible. Any EXECUTE AS change is automatically reverted at the end of a procedure.

Other things that dont live past the scope of the procedure are changes using the SET command and #TempObjects.

However, what you could do is encapsulate the logic that needs to be executed under the different security context in a procedure and then call that from within the procedure that changes the context. Something like:

CREATE PROCEDURE dbo.CallWithImpersonateUser
   @ProcedureName
AS
BEGIN
  EXECUTE AS USER = 'UserName';
  EXEC @ProcedureName;
END;

Problem

I'm using SQL Server 2008 R2 on a Windows 7 box. I have created a stored procedure to run the SQL command `EXECUTE AS USER`: ``` CREATE PROCEDURE dbo.ImpersonateUser AS BEGIN EXECUTE AS USER = 'UserName'; END ``` Then, I'm trying to see if I am impersonating the user... ``` PRINT CURRENT_USER; EXEC ImpersonateUser; PRINT CURRENT_USER ``` ...and the result is... ``` dbo dbo ``` ...when I want it to be... ``` dbo UserName ``` This doesn't work, of course, because the `EXECUTE AS USER` statement is only valid inside the stored procedure `ImpersonateUser`. My question is this: Is it possible for the stored procedure `ImpersonateUser` to affect the calling context (session)? I want to encapsulate (and hide) other logic in the stored procedure.

Original source