Yesterday, I shared a blog post discussing how to list all the users with System Admin (sysadmin) rights in SQL Server. Since then, I have received numerous emails and comments on this subject, with one comment in particular catching my attention.
SQL Expert Chris Mangrum shared an interesting script modification that includes an additional permission called Control Server. Here is the alternate script shared by Chris:
USE master
GO
SELECT DISTINCT p.name AS [loginname],
p.type,
p.type_desc,
p.is_disabled,
s.sysadmin,
CONVERT(VARCHAR(10), p.create_date, 101) AS [created],
CONVERT(VARCHAR(10), p.modify_date, 101) AS [update]
FROM sys.server_principals p
JOIN sys.syslogins s ON p.sid = s.sid
JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
-- Logins that are not process logins
AND p.name NOT LIKE '##%'
-- Logins that are sysadmins or have GRANT CONTROL SERVER
AND (s.sysadmin = 1 OR sp.permission_name = 'CONTROL SERVER')
ORDER BY p.name
GOWhen you run the above script, you will get a result set that includes additional details compared to the original script I shared yesterday. I personally find this new script to be more comprehensive in identifying system administrators, and I highly recommend it to my audience.
As a consultant, when I engage in a Comprehensive Database Performance Health Check, I do not ask for a username and password from my users. Instead, I expect the DBA with whom I am working to have system admin rights. This allows me to guide them through the necessary steps to optimize their SQL Server without taking over their session. I believe this is the best approach to assist users effectively.
Understanding SQL Server permissions is crucial for database administrators and developers alike. By utilizing scripts like the one shared by Chris, you can easily identify users with sysadmin rights and Control Server permission. This knowledge empowers you to manage and secure your SQL Server environment efficiently.
Stay tuned for more SQL Server tips and tricks in future blog posts!