Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

WITH EXECUTE AS SELF not behaving as expected Expand / Collapse
Author
Message
Posted Friday, August 02, 2013 12:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 10:30 AM
Points: 44, Visits: 99
I'm trying to understand EXECUTE AS SELF/security contexts. In all the documentation I have found, the explanation is that this clause in a module will cause the module to execute as the user who issued the CREATE <module> or ALTER <module> statement. Seems pretty straight forward, but when I test it, I don't get expected results:

When I execute these two statements:

SELECT * FROM sys.login_token;
SELECT * FROM sys.user_token;

As expected, I get a set of tokens listed for me, showing my login. Let's call this result MyTokens.

Next, I put the two statements into a stored procedure that I create, and I include WITH EXECUTE AS SELF:

CREATE PROC dbo.DisplayExecutionContext
WITH EXECUTE AS SELF
AS
SELECT * FROM sys.login_token;
SELECT * FROM sys.user_token;
GO

Since I am the user who issued this CREATE PROC statement, SELF refers to me, so when I execute this proc (when anyone executes it), I should see the same MyTokens I got before. Nope. Instead, I get a different set of tokens, one belonging to sa.

It seems to me that I am being recognized as one user when I run the statements directly but as sa when I run the stored procedure as... me (SELF)! No doubt I am missing something. Can someone please tell me what it is? Thank you.
Post #1480257
Posted Friday, August 02, 2013 3:20 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:43 AM
Points: 756, Visits: 631
When you add the EXECUTE AS clause to a procedure you impersonate a database user, not a server login. If you are logged in with sysadmin rights when you create the procedure, the procedure will be owned by dbo, and if sa owns the database, sys.server_token will include sa.

I have an article on my web site where I discuss EXECUTE AS, certificate signing and other permissions functions in detail: http://www.sommarskog.se/grantperm.html.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1480626
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse