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
 Transact-SQL (2008)
 WITH EXECUTE_AS not working for sproc.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 07/31/2013 :  16:27:14  Show Profile  Reply with Quote
I have the following sproc which is trying to gather information from the plan cache, but it fails when executed. Relevant information: POL_SSV is a SQL Server login with VIEW SERVER STATE permission.

ALTER PROCEDURE [dbo].[Sproc_Execution_Plan_Get]
(
 @Stored_Procedure_Name VARCHAR(200)
)
WITH EXECUTE AS 'POL_SSV'
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Purpose: Return execuction plan in XML to help our partners with performance tuning

Declare 
 @Stored_Procedure_Specific_Name VARCHAR(128),
 @Plan_Handle VARCHAR(75)
 
SELECT @Stored_Procedure_Specific_Name = SP.Stored_Procedure_Specific_Name
FROM Stored_Procedure AS SP
WHERE SP.Stored_Procedure_Name = @Stored_Procedure_Name
 AND SP.PUN = 685563--CAST(PARSENAME(SUSER_NAME(),1) AS INT)

--THIS IS WHERE IT FAILS!!!
SELECT @Plan_Handle = CP.plan_handle
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE OBJECT_NAME(st.objectid,st.dbid) = @Stored_Procedure_Specific_Name


One thing to note: I have tried using "SETUSER" on this DB (which is SQL 2008 EE), and I get the following error:
Setuser failed because of one of the following reasons: the database principal 'POL_SSV' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.

However, the server principal and database principal exist, have access to the database, and there is nothing special that should prevent them from being impersonated. I get this error no matter what user I try to impersonate.

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 08/01/2013 :  03:00:42  Show Profile  Reply with Quote
which account are you executing the procedure from? yur own logged in account?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 08/01/2013 :  07:51:41  Show Profile  Reply with Quote
The goal is to have an end user account be able to execute it, but it fails when executing it using my account, which is a sysadmin level.
Go to Top of Page

troubleshootingsql
Starting Member

3 Posts

Posted - 08/01/2013 :  09:15:30  Show Profile  Reply with Quote
Have you tried something like this:
alter PROCEDURE [dbo].[Sproc_Execution_Plan_Get]
(
@Plan_Handle VARCHAR(75)
)
AS
SET NOCOUNT ON
execute as login = '<use which has view server state privs>';
select * from sys.dm_exec_cached_plans

Amit Banerjee
www.TroubleshootingSQL.com
www.facebook.com/TroubleshootingSQL
http://twitter.com/banerjeeamit
Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 08/01/2013 :  09:59:59  Show Profile  Reply with Quote
When I try it with an SA account, it works. However, when I try it with a user account, I get: Msg 15406, Level 16, State 1, Procedure Sproc_Execution_Plan_Get, Line 11
Cannot execute as the server principal because the principal "POL_SSV" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Go to Top of Page

troubleshootingsql
Starting Member

3 Posts

Posted - 08/01/2013 :  10:16:21  Show Profile  Reply with Quote
The non-sysadmin login should be mapped as a database user in which the stored procedure exists.

Amit Banerjee
www.TroubleshootingSQL.com
www.facebook.com/TroubleshootingSQL
http://twitter.com/banerjeeamit
Go to Top of Page

troubleshootingsql
Starting Member

3 Posts

Posted - 08/01/2013 :  10:56:50  Show Profile  Reply with Quote
Sequence of events:
1. Create a plan_admin login and grant it view server state priv
2. Create a retrieve_plan login and grant it impersonate priv on the plan_admin login
3. Create the procedure to fetch the plan using the EXECUTE AS LOGIN priv in a user database
4. Create a database user for both logins in #1 and #2
5. Grant execute permissions on the procedure created in #4 to retrieve_plan user

The above works in my case

Amit Banerjee
www.TroubleshootingSQL.com
www.facebook.com/TroubleshootingSQL
http://twitter.com/banerjeeamit
Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 08/01/2013 :  13:26:47  Show Profile  Reply with Quote
Thanks Amit. I was trying to avoid having to grant specific permissions to several hundred logins by using this sproc. With the GRANT IMPERSONATE requirement, it does not seem like that is possible, which is unfortunate. I might as well just grant VIEW SERVER STATE to all 200 users.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/01/2013 :  13:47:21  Show Profile  Reply with Quote
I'd sugges that you don't (and shouldn't) do it by user. Rather you should set up a Database Role. You assign that role permissons in the DB. Then you can either add the users to that role or create a security group in Windows/Active Directory and use that to manage access.
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.09 seconds. Powered By: Snitz Forums 2000