List admins on SQL Server

Article for: Amazon Redshift Vertica

Query below returns list of logins in current SQL Server instance with admin privileges.

Query

select mp.name as login,
       case when mp.is_disabled = 1 then 'Disabled'
            else 'Enabled'
            end as status,
      mp.type_desc as type
from sys.server_role_members srp 
join sys.server_principals mp 
     on mp.principal_id = srp.member_principal_id
join sys.server_principals rp 
     on rp.principal_id = srp.role_principal_id
where rp.name = 'sysadmin'
order by mp.name;

Columns

  • login - login
  • status - status of the login
    • Enabled
    • Disabled
  • type - principal type

Rows

  • One row represents one user (sysadmin) in the database
  • Scope of rows: all admins in the database
  • Ordered by login

Sample results

sample results

0
There are no comments. Click here to write the first comment.