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.
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'ASSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED-- Purpose: Return execuction plan in XML to help our partners with performance tuningDeclare @Stored_Procedure_Specific_Name VARCHAR(128), @Plan_Handle VARCHAR(75) SELECT @Stored_Procedure_Specific_Name = SP.Stored_Procedure_Specific_NameFROM Stored_Procedure AS SPWHERE 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_handleFROM sys.dm_exec_cached_plans AS CPCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS stWHERE 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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))ASSET NOCOUNT ONexecute as login = '<use which has view server state privs>'; select * from sys.dm_exec_cached_plansAmit Banerjeewww.TroubleshootingSQL.comwww.facebook.com/TroubleshootingSQLhttp://twitter.com/banerjeeamit |
|
|
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 11Cannot 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. |
|
|
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 Banerjeewww.TroubleshootingSQL.comwww.facebook.com/TroubleshootingSQLhttp://twitter.com/banerjeeamit |
|
|
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 priv2. Create a retrieve_plan login and grant it impersonate priv on the plan_admin login3. Create the procedure to fetch the plan using the EXECUTE AS LOGIN priv in a user database4. Create a database user for both logins in #1 and #25. Grant execute permissions on the procedure created in #4 to retrieve_plan userThe above works in my caseAmit Banerjeewww.TroubleshootingSQL.comwww.facebook.com/TroubleshootingSQLhttp://twitter.com/banerjeeamit |
|
|
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. |
|
|
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. |
|
|
|
|
|
|
|