SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Trouble with EXECUTE AS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DesertDude
Starting Member

USA
8 Posts

Posted - 11/17/2011 :  09:21:34  Show Profile  Reply with Quote
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

USA
8 Posts

Posted - 11/17/2011 :  14:44:36  Show Profile  Reply with Quote
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.
Go to Top of Page

DesertDude
Starting Member

USA
8 Posts

Posted - 11/17/2011 :  16:37:47  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000