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
 Transact-SQL (2008)
 WITH EXECUTE_AS not working for sproc.

Author  Topic 

MuadDBA

628 Posts

Posted - 2013-07-31 : 16:27:14
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

52326 Posts

Posted - 2013-08-01 : 03:00:42
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

628 Posts

Posted - 2013-08-01 : 07:51:41
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 - 2013-08-01 : 09:15:30
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

628 Posts

Posted - 2013-08-01 : 09:59:59
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 - 2013-08-01 : 10:16:21
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 - 2013-08-01 : 10:56:50
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

628 Posts

Posted - 2013-08-01 : 13:26:47
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-01 : 13:47:21
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
   

- Advertisement -