6

I need to run sp_Blitz and a bunch of DMV queries from Glenn Berry's Dr DMV set for SQL 2016. The documentation for sp_Blitz on BrentOzar.com states "Sysadmin permissions. sp_Blitz® checks a lot of system-level diagnostic data."

Is sysadmin really the minimum required permission level or has anybody been able to run this with a more restrictive permission level?

Not sure that I am going to get away with asking for sysadmin privilege on a client's production server.

10

Yes, it requires sa permissions. But since it's a stored procedure, you can get permissions via a certificate

See the section How to Grant Permissions to Non-DBAs

USE master;
GO
CREATE CERTIFICATE sp_BlitzFirst_cert
ENCRYPTION BY PASSWORD = '5OClockSomewhere'
WITH SUBJECT = 'Certificate for sp_BlitzFirst',
START_DATE = '20130711', EXPIRY_DATE = '21000101';
GO
CREATE LOGIN sp_BlitzFirst_login FROM CERTIFICATE sp_BlitzFirst_cert;
GO
CREATE USER sp_BlitzFirst_login FROM CERTIFICATE sp_BlitzFirst_cert;
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO sp_BlitzFirst_login;
GO
GRANT CONTROL SERVER TO sp_BlitzFirst_login;
GO
ADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert
WITH PASSWORD = '5OClockSomewhere';
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public];
GO

Then whenever you update the proc, you’ll need to reassign the permissions by adding the signature again:

ADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert
WITH PASSWORD = 'Get lucky';
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public];
GO

Hope this helps!

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.