EXECUTE AS doesn't seem to be behaving as I'd expect. My login maps to dbo login and dbo user in my database. I created a proc that produces a result set. If I ALTER that proc to EXECUTE AS OWNER, I see a different resultset. This is unexpected to me because I created the proc and so I'm the owner, right? Here is an example of this using AdventureWorks2008R2.
Thank you
USE tempdb;
GO
SELECT User
GO
RESULT:
---------------------------
dbo
(1 row(s) affected)
---------------------------
CREATE PROC dbo.DisplayExecutionContext
AS
SELECT * FROM sys.login_token;
SELECT * FROM sys.user_token;
GO
EXEC dbo.DisplayExecutionContext;
GO
<RESULTS HAVE SEVERAL RECORDS>
--Alter proc to EXECUTE AS OWNER
ALTER PROC dbo.DisplayExecutionContext
WITH EXECUTE AS OWNER
AS
SELECT * FROM sys.login_token;
SELECT * FROM sys.user_token;
GO
EXEC dbo.DisplayExecutionContext;
GO
<RESULTS ARE MUCH SMALLER NOW>
Trouble with EXECUTE AS
Posted - 11/17/2011 : 09:21:34
