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.

Solarwinds

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
ORDER BY name

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)

Solarwinds
, , ,
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

4 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
    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
  • Hi Pinal,
    Yes, I’m sa and also the only one.

    Reply

Leave a Reply

Menu