SQL SERVER – List Users with System Admin (sysadmin) Rights – Part 2

Yesterday I shared a blog post where I discussed how to list all the users with the System Admin (sysadmin) rights. I have received a quite a few emails and comments on this subject. The comment which got the most attention is from SQL Expert Chris Mangrum. He has shared a very interesting script with modification from the original script where he has also included additional permission of Control Server.

Here is the alternate script shared by SQL Expert 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
GO

When you run above script you get following result set:

You can see that this script includes few additional details along with original script which I had shared yesterday. I personally liked this new script to identify the system admin and I am going forward to suggest the same to my audience.

As a consultant, when I engage in Comprehensive Database Performance Health Check I do not ask for a username and password to my users. I just expect that DBA with whom I am working has system admin rights. This way, I can guide them with the exact necessary steps to perform on their SQL Server without me taking over their session. I believe this is the best possible method to help users.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.