Though the script sounded simple to me, but I found that there are lots of incorrect scripts available on the internet. Here is the one of the script I found to find out the details of the system admin.
Please note that following script is not accurate and I do not encourage you to depend on it. You will find the correct script at the end of this article, please continue reading till the end of the blog post.
SELECT name,type_desc,is_disabled FROM master.sys.server_principals WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1 ORDER BY name
Above script looks very simple. When I ran on my machine I got below.
I realized that some entries are missing. So, I went ahead and checked the properties of SysAdmin role and found below
As we can see, I am not seeing all 6 members in the output. So, here is the query which I was able to write which would give accurate information.
SELECT 'Name' = sp.NAME ,sp.is_disabled AS [Is_disabled] FROM sys.server_role_members rm ,sys.server_principals sp WHERE rm.role_principal_id = SUSER_ID('Sysadmin') AND rm.member_principal_id = sp.principal_id
Here is the output, which is accurate.
Do you have any similar interesting queries? Please share them with other readers via the comments section.
Reference: Pinal Dave (https://blog.sqlauthority.com)