WITH EXECUTE AS SELF not behaving as expected

  • 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.

  • 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