patternsqlModerate
Minimum permissions required to run sp_Blitz
Viewed 0 times
sp_blitzpermissionsminimumrequiredrun
Problem
I need to run
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.
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.
Solution
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
Then whenever you update the proc, you’ll need to reassign the permissions by adding the signature again:
Hope this helps!
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];
GOThen 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];
GOHope this helps!
Code Snippets
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];
GOADD SIGNATURE TO sp_BlitzFirst BY CERTIFICATE sp_BlitzFirst_cert
WITH PASSWORD = 'Get lucky';
GO
GRANT EXECUTE ON dbo.sp_BlitzFirst TO [public];
GOContext
StackExchange Database Administrators Q#188192, answer score: 11
Revisions (0)
No revisions yet.