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.