August 2, 2013 at 12:39 am
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.
August 2, 2013 at 3:20 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply