CODEWITHSUNDEEP
sql-server-2008-r2impersonation
HydroPowerDeveloper
HydroPowerDeveloper

Reputation: 3497

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

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.

Upvotes: 3

Views: 10347

Answers (2)

tomuxmon
tomuxmon

Reputation: 93

Answer by Sebastian is correct, but still you have to grant impersonate for calling user. Syntax can be found here

GRANT IMPERSONATE ON LOGIN::[login_to_be_impersonated] to [login1];
GO

Upvotes: 2

Sebastian Meine
Sebastian Meine

Reputation: 11773

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;

Upvotes: 7

Related Questions