Non-Sysadmins can run SPs Too!

One of the things I have been focusing on lately is improving the processes that are currently in place and to automate tasks to give myself (and my colleague) more time focusing on larger projects. Most recently, I began writing some knowledge base for our help desk that would walk agents through basic troubleshooting steps. The hope was to have them gather some information up front (active connections, blocking sessions etc) to help speed up the resolution time for us. Since the agents are not system admins, they lack the necessary rights to gather certain data…or do they?

Parks And Recreation Reaction GIF

The following articles ( Brent Ozar and Adam Machanic) walk through how to create “certificates” that, through steps you can follow through, will allow non-sysadmins the ability to execute specific stored procedures. In my example, I will use sp_whoisactive (sp for activity monitoring), but ANY stored procedure will work.

Creating A Certificate

USE master 
GO

CREATE CERTIFICATE sp_whoisactiveCERT
ENCRYPTION BY PASSWORD = 'EnterYourPasswordHERE' 
WITH SUBJECT = 'Who is Active', 
--START_DATE = 'If you want the cert to activate at a certain time, enter it here'
EXPIRY_DATE = '9999-12-31' 
GO

Great, now we have the certificate created, we can move on to creating the user from the certificate.

Creating The User

CREATE LOGIN sp_whoisactive_login
FROM CERTIFICATE sp_whoisactiveCERT
GO

Simple enough right? The next bit is to grant permissions, and since I want this to be as “tied down” as much as possible, we are going to grant the login VIEW SERVER STATE. This will grant the ability to return server state information from Dynamic Management Views and Functions, that can be used to monitor the health of a server and diagnose issues. I do not want the agents to have the ability to anything more than that. More on Dynamic Management Views and Functions can be found here.

Granting Permissions

GRANT VIEW SERVER STATE 
TO sp_whoisactive_login
GO

With the necessary rights applied, we are ready to move to the final step. We will need to add the signature to the actual stored procedure (sp_whoisactive in this case).

Adding A John Hancock

ADD SIGNATURE TO sp_WhoIsActive 
BY CERTIFICATE sp_whoisactiveCERT
WITH PASSWORD = 'This is the same password you used for the certificate creation' 
GO

I should mention, it would help if you have sp_whoisactive installed first before you execute the last steps. But after that you are done!

Final Remarks

Huge shout out to Brent and Adam for providing the great details on how to utilize this “feature” in SQL Server, and making it easy to implement. As I said earlier, you can really implement this with any stored procedure. In a future post, I will be showing off a simple powershell script that will help you keep track of updating any of your “third-party” stored procedures that you have implemented into your arsenal across multiple servers. Until next time, thanks for reading, leave a comment below, or reach out to me if you have any questions!