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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
select loginname,sysadmin,createdate,updatedate,type_desc,type from
syslogins s join sys.server_principals r on s.loginname=r.name
where sysadmin=1
why you can’t try this for same result.
Did you miss Control Server permission?