Execute on DMVs without user having View Server State

,

Wanted to share this script to the community just in case anyone out there may be search for a way to allow a hosted customer the ability to query certain DMVs for information without granting them View Server State. There are some other options out there, but this worked better for me after going through different methods, and reading through several different community blogs.

/*
Purpose:
  This script is intended to provide database customers
with an alternative way of executing on a system objects
without gaining the right to the {View Server State} 
permission.

Description:
   The script assumes that the LOGIN is already created
and has a USER account in the specified database. The process
will allow a non privileged user the ability to execute a stored
proc that calls on a DMV to gain infomation regarding session activity.
Because the stored procedure is based in the user database a certificate signature was also attached to the Stored proc
in order to control the security risk of change to the proc in order to gain more
visibility.

--Logic
 1. A stored proc is created in the user database.
 2. A certificate is created within the user database
 3. Use the newly created certificate to add a signature to the proc
 4. Make a copy of the user database certificate and house it in the master database
 5. Create login that will act as proxy from the certificate created in the master database
 6. Grant {View Server State} to the certificate mapped LOGIN
 7. Finally grant execute on the stored proc to the database user
*/

USE ['User Database Here'];
GO

CREATE PROCEDURE ['Proc Name Here']
AS

BEGIN
SET NOCOUNT ON;

--Example of querying on a DMV for full resultset
SELECT 
     [session_id],
	 [login_time],
	 [host_name],
	 [host_process_id],
	 [nt_user_name],
	 [login_name],
	 [context_info]
FROM sys.dm_exec_sessions
WHERE database_id IN ('database ids here if needed') --Databases pertaining to the customer if hosting multiple customers

END
GO

--Create Cert in the User DB
CREATE CERTIFICATE [Permission$ViewServerState]
ENCRYPTION BY PASSWORD = '$tr0ngP@ssw0rd'
WITH SUBJECT = 'VIEW SERVER STATE permission',
EXPIRY_DATE = '2025-12-31';

ADD SIGNATURE
TO [dbo].['Proc Name Here']
BY CERTIFICATE [Permission$ViewServerState]
WITH PASSWORD = '$tr0ngP@ssw0rd';

DECLARE @Cert NVARCHAR(4000) = CONVERT(NVARCHAR(4000),CERTENCODED(CERT_ID(N'Permission$ViewServerState')), 1);

--Use master to create a cert based on the user database cert 
EXEC (N'USE [master];
CREATE CERTIFICATE [Permission$ViewServerState]
FROM BINARY = ' + @Cert);

--Create Proxy LOGIN and grant VSS permission
EXEC (N'USE [master];
CREATE LOGIN [Permission$ViewServerState_User]
FROM CERTIFICATE [Permission$ViewServerState];

GRANT VIEW SERVER STATE TO [Permission$ViewServerState_User];
');

--Final grant user Execute or Select(If a Table-valued Function is required) on the object
USE ['User Database Here'];
GO

GRANT EXECUTE ON OBJECT::[dbo].['Proc Name Here'] TO ['Execuiting User Name Here'];

Rate

Share

Share

Rate