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

One of the pre-requesting of Comprehensive Database Performance Health Check is to check if user has necessary system admin rights or not. It is almost impossible to help customer with SQL Server Performance if they do have a DBA who has access to system admin rights.

SQL SERVER - List Users with System Admin (sysadmin) Rights sysadminrights-800x305

Please note that I NEVER ask for any username or password, during the consultation. My performance tuning technique is such where I will guide you step by step about how you can tune your SQL Server yourself. I teach you with every single click and also I teach you how to use all the scripts which I provide during consultancy. Everything is scientific and systematic. However, there is one requirement in this arrangement.

The DBA who is on the other side of the computer MUST have a system admin rights. If he doe snot have enough rights we will be able to do only limited exercise with regards to performance. Most of my diagnosis scripts only work with sysadmin rights.

Here is the quick script which you can run and list all the users with admin rights. If you find your username there, you know you are an admin.

SELECT name,type_desc,is_disabled, create_date
FROM master.sys.server_principals
WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1

Let me know if you use any other script. I will be happy to publish that on blog with due credit to you.

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

, SQL Scripts, SQL Server, SQL Server Security
Previous Post
SQL SERVER- New DMF in SQL Server 2017 – sys.dm_os_file_exists. A Replacement of xp_fileexist
Next Post
SQL SERVER – List Users with System Admin (sysadmin) Rights – Part 2

Related Posts

5 Comments. Leave new

  • I found most of this script somewhere so I can’t take credit for it. I tweaked it a bit to include logins with CONTROL_SERVER as well since we sometimes grant that to developers but want to limit sysadmin:

    USE master
    SELECT DISTINCT p.name AS [loginname] ,
    p.type ,
    p.type_desc ,
    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
    — 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

  • Hi Pinal,
    Yes, I’m sa and also the only one.

  • An issue with your query is that if will show sysadmin role for an individual account that has permissions through membership on an AD group with sysadmin privileges, even if the individual account has sysadmin disabled. In order to avoid this issue; we simply use “select name, sysadmin from syslogins where sysadmin = 1;”


Leave a ReplyCancel reply

Exit mobile version