Trouble with EXECUTE AS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Trouble with EXECUTE AS

Author

Topic

DesertDude
Starting Member

8 Posts

Posted - 2011-11-17 : 09:21:34

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>

DesertDude
Starting Member

8 Posts

Posted - 2011-11-17 : 14:44:36

Me again. I just want to clarify that even if I use EXECUTE AS 'dbo' I get the same symptom. Even though I'm logged in as dbo. Thanks again.

DesertDude
Starting Member

8 Posts

Posted - 2011-11-17 : 16:37:47

OK, so I now understand what is happening here. Here's the solution for anyone who is interested:

WITH OWNER is at the database level, which is dbo. So dbo can only see what's in that database since it is a user and not a login. If you change the proc to use WITH Caller, then it works just like it would without the EXECUTE AS clause. Caller is the default. dbo is a database user, but the human being who is interacting with SSMS is a login. The login, in the case above, is an administrator, so when the proc is created without the EXECUTE AS clause, it's giving back all the tokens that the administrator can see. When it's run as dbo, or owner, it only sees the tokens that those database users can see.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources