SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


WITH EXECUTE AS SELF not behaving as expected


WITH EXECUTE AS SELF not behaving as expected

Author
Message
peter 82125
peter 82125
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 140
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.
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2135 Visits: 872
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search