SQL SERVER – Query to Get the List of Logins Having System Admin (sysadmin) Permission

SQL
8 Comments

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.

SQL SERVER - Query to Get the List of Logins Having System Admin (sysadmin) Permission who-sysadmin-01

Solarwinds

I realized that some entries are missing. So, I went ahead and checked the properties of SysAdmin role and found below

SQL SERVER - Query to Get the List of Logins Having System Admin (sysadmin) Permission who-sysadmin-02

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.

SQL SERVER - Query to Get the List of Logins Having System Admin (sysadmin) Permission who-sysadmin-03

Do you have any similar interesting queries? Please share them with other readers via the comments section.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – How to get historical deadlock Information from System Health Extended Events?
Next Post
SQL SERVER – Why Cluster Network is Unavailable in Failover Cluster Manager?

Related Posts

8 Comments. Leave new

  • Thank you very much for your script.

    With respect, sir, would the ANSI-92 join syntax be better?

    SELECT ‘Name’ = sp.NAME
    ,sp.is_disabled AS [Is_disabled]
    FROM sys.server_role_members rm
    inner join sys.server_principals sp on rm.member_principal_id = sp.principal_id
    WHERE rm.role_principal_id = SUSER_ID(‘Sysadmin’)

    Reply
  • I can’t remember where I found the original part of this script, but I know I borrowed it from somewhere. I was also interested in finding all the sysadmins on a server, but also wanted to list any logins with the “Control Server” permission on boxes where server roles could be used to elevate rights without resorting to sysadmin. I tweaked it to include logins with Control Server, but return 0 for the sysadmin column.

    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

    Reply
  • prefer my script

    SELECT sp.name AS ServerPrincipal,
    sp.type_desc AS LoginType,
    CASE sp.is_disabled
    WHEN 0 THEN ‘No’
    WHEN 1 THEN ‘Yes’
    END AS UserDisabled,
    sp.create_date AS DateCreated,
    sp.modify_date AS DateModified,
    sp.default_database_name AS DefaultDB,
    sp.default_language_name AS DefaultLang,
    ISNULL(STUFF((SELECT ‘,’ +
    CASE
    WHEN ssp22.name = ‘sysadmin’ THEN ssp22.name + ‘ “Danger Will Robinson”‘
    ELSE ssp22.name
    END
    FROM sys.server_principals ssp2
    INNER JOIN sys.server_role_members ssrm2
    ON ssp2.principal_id = ssrm2.member_principal_id
    INNER JOIN sys.server_principals ssp22
    ON ssrm2.role_principal_id = ssp22.principal_id
    WHERE ssp2.principal_id = sp.principal_id
    ORDER BY sp.name
    FOR XML PATH(N”), TYPE).value(N’.[1]’, N’nvarchar(max)’), 1, 1, N”), ‘NoRolesHeld’) AS ListofServerRoles
    FROM sys.server_principals sp
    LEFT OUTER JOIN sys.server_permissions sper ON sp.principal_id = sper.grantee_principal_id
    WHERE sp.type IN (‘S’,’G’,’U’) AND sp.name NOT LIKE ‘##%##’
    GROUP BY sp.name, sp.type_desc, sp.is_disabled, sp.create_date, sp.modify_date,
    sp.default_database_name, sp.default_language_name, sp.principal_id
    ORDER BY ServerPrincipal

    You also want to know who has explicit permission such as ‘CONTROL SERVER’, this is the same as sysadmin but doesnt show up as a role but a server level securable. For this i use

    SET CONCAT_NULL_YIELDS_NULL OFF

    select ‘Servername is ‘ + @@SERVERNAME + CHAR(10) + CHAR(13)

    union all

    select ‘Server principal ”’ + sp.name + ”’ holds SQL Server role ”’ + sp2.name + ””
    from sys.server_principals sp
    inner join sys.server_role_members srm on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals sp2 on srm.role_principal_id = sp2.principal_id
    WHERE sp.principal_id > 4

    UNION ALL

    select ‘Server principal ”’ + sp.name + ”’ is a ‘ +
    sp.type_desc collate Latin1_General_CI_AS_KS + ‘ created on ”’ +
    CAST(sp.create_date AS VARCHAR(25)) + ”’, last modified on ”’ +
    CAST(sp.modify_date AS VARCHAR(25)) + ”’, default database is [‘ +
    sp.default_database_name + ‘], with ‘ +
    CASE srp.state_desc
    WHEN ‘GRANT’ THEN ‘Granted’
    WHEN ‘DENY’ THEN ‘Denied’
    END + ‘ permission ‘ + srp.class_desc + ‘ -> ‘ + srp.permission_name
    from sys.server_principals sp
    inner join sys.server_permissions srp on sp.principal_id = srp.grantee_principal_id
    inner join sys.server_principals sp2 on srp.grantor_principal_id = sp2.principal_id
    where
    sp.principal_id > 256 AND sp.name NOT LIKE ‘NT[^][AS][UE][TR][HV]%\%’ AND sp.name NOT LIKE ‘##MS%##’
    and srp.permission_name NOT IN (‘CONNECT SQL’, ‘CONNECT’)

    Reply
  • simple is better:

    exec sp_helpsrvrolemember @srvrolename=’sysadmin’
    go

    Reply

Leave a Reply

Menu