A question was posted on Twitter’s #sqlhelp hashtag on how to allow non-sysadmin accounts or accounts which do not have the View Server State privilege to fetch the cached query plans. This can be a very big deal if you are a hosting company for databases. You would want to restrict access to all non-admin users but at the same time you might need to give your customers and their developers access to their query plans to assist with their query tuning efforts.
The first thing that comes to mind is to use impersonation in SQL Server. Implementing it is where the fun is!
Let’s consider this hypothetical scenario. I have a SQL Server login named Sentinel which would be granted View Server State privileges only. I have another SQL Server login named Agent which is neither a sysadmin nor a login with View Server State privileges. The Agent login need to look up plans of stored procedures which are executing in the current database.
The first task would be to grant View Server State privileges to Sentinel and impersonate privileges to Agent.
USE [master]
GO
GRANT VIEW SERVER STATE TO [sentinel];
GRANT IMPERSONATE ON LOGIN::[sentinel] to [agent];
GO
Next I create a stored procedure which allows the login agent to see the cached plans which are available for the current database context.
CREATE PROCEDURE [dbo].[usp_SeePlan]
AS
BEGIN
SET NOCOUNT ON
EXECUTE AS LOGIN = ‘sentinel’;
SELECT ‘Executed as user: ‘ + SUSER_NAME()
SELECT * FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (plan_handle) qt
WHERE qp.dbid = db_id();
/* Add your diagnostic query here
The above query is an example */
REVERT;
SELECT ‘Reverted back to user: ‘ + SUSER_NAME()
END
Next when I attempt to grant permissions to the login agent to execute the above stored procedure, I will get the error shown below:
Msg 15151, Level 16, State 1, Line 1
Cannot find the user ‘agent’, because it does not exist or you do not have permission.
This error is reported because there is no database user mapped to the login agent. So I create a database user for agent and grant execute permissions to the login agent for the stored procedure using the commands below:
CREATE USER [agent] FOR LOGIN [agent] WITH DEFAULT_SCHEMA=[dbo];
GRANT EXECUTE ON OBJECT::[usp_SeePlan] TO agent;
Next I will try to execute the stored procedure with the agent login. Everything should work now! But alas.. I get another error as shown below:
Msg 916, Level 14, State 1, Procedure usp_SeePlan, Line 5
The server principal “sentinel” is not able to access the database “Test” under the current security context.
This is because of the fact the login sentinel does not exist in the database Test in which the stored procedure exists. Next I will create a database user mapped to the login sentinel.
CREATE USER [sentinel] FOR LOGIN [sentinel] WITH DEFAULT_SCHEMA=[dbo]
Now when I used the stored procedure, everything works!! Screenshot of the output is shown below.
A few other caveats to note is that if the stored procedure is created with the WITH EXECUTE AS option, then on execution of the procedure, you will be reported with the following error:
Msg 297, Level 16, State 1, Procedure usp_SeePlan, Line 8
The user does not have permission to perform this action.
So the summary is the following sequence:
1. Grant impersonate rights to Agent for Sentinel
2. Create a stored procedure which does the work which requires View Server State privilege using the EXECUTE AS LOGIN = ‘Sentinel’ statement
3. Create two database users in the database which are mapped to Agent and Sentinel
4. Grant execute privileges on the stored procedure which was created in Step #2.
Voila… You are now ready to impersonate and view the query plan!
Like this:
Like Loading...
TroubleshootingSQL 