Who are the sysadmins in this sql server?


Follow me on twitter: @MarlonRibunal

Sysadmins are pretty much powerful in the SQL Server environment. They can put the whole SQL Server infrastructure under their control. That said, it is important to know who else has this fixed server role.

Here’s a quick query that you can run to find out the users with sysadmin fixed server role. Sysadmins have a complete control of your server. So, it is very important to know who these people are.

Just to give you an example on why it is very important to check who’s who in your server from time to time, Team Shatter has recently published an advisory on Privilege Escalation Via Internal SQL Injection In RESTORE DATABASE Command.

According to the advisory, there is a vulnerability in the RESTORE DATABASE command that allows malicious users to run SQL codes via internal sql injection. This vulnerability can be exploited by users with CREATE DATABASE permission.

A rogue user can find his way to taking control over your databases by using a backdoor such as the vulnerability described above. Imagine the same user was able to add himself to a server-wide role such as the sysadmin.

Find Sysadmins with TSQL Query

So, going back to the query, here is what it looks like:

USE master

SELECT  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
        -- Logins that are not process logins
        AND p.name NOT LIKE '##%'
        -- Logins that are sysadmins
        AND s.sysadmin = 1

And that query should give us something like this:

sysadmins - TSQL To Find Logins With Sysadmin Fixed Server Role In SQL Server 2008 2012

Find Sysadmins with System Stored Procedure

There is a quicker way to do that, though. You’ll achieve a similar result by executing the system stored procedure sp_helpsrvrolemember:

 EXEC sp_helpsrvrolemember 'sysadmin'

And, you’ll get a result that looks like this:

System Stored Procedure sp_helpsrvrolemember to return logins with certain role in sql server

Of course, you can use any of the other server roles with sp_helpsrvrolemember as parameters. For example, if you want to look for users with the database creator role, you use the variable 'dbcreator' instead of 'sysadmin'.

Server roles are listed somewhere in one of the system tables in the master database. But there’s already a system stored procedure for that as well –  sp_helpsrvrole. You don’t have to dig in and search for them yourself. SQL Server gives you most of the stored procedures you’ll likely need out of the box.

 EXEC sp_helpsrvrole

Executing that will give you the following list:

Listing all the server roles in SQL Server

Update from the comments:

Here’s a good one from Eric Russell (see comment below): to return what accounts are members of a domain group, we do this:

 xp_logininfo 'DEVCOMPUTER\SQLAdmins', 'members';

*suppose SQLAdmins is a domain group

And, it does exactly what it is meant to do – returns the members of the group like this:

who are the domain members of the Windows Group in my SQL Server

Author: Marlon Ribunal

I'm here to learn and share things about data and the technologies around data.


  1. Thanks Marlon, again thanks for sharing this.

  2. Hi,
    I manager server window. And i have big problem on SQL server. when i uncheck sysadmin , my website will be error can not login to user DB and passDB …, but when i check in to box sysadmin on security, all user remote to sql server can see DB of other. Please help me. I don’t want my customer lose data.

  3. Hello Marlon,

    Do you have this situation:

    1) login X have sysadmin role

    2) .net application have error:
    The server principal “X” is not able to access the database “myDB” under the current security context.

    3) We added User Mapping (db_owner on “myDB”), (last attempt), and error disappear.

    Is this normal?
    sysadmin + databse mapping?

    I don-t have a explanation. Do you have any idea?

  4. You will also want to look for members of the securityadmin role (which BOL considers equivalent to sysadmin since it allows you to grant yourself or others sysadmin permissions) and individuals with CONTROL SERVER which is also similar to sysadmin. The main difference is that individual permissions can be denied to someone with CONTROL SERVER but not a member of the sysadmin role.

  5. Hi,

    Is it possible that I do get the members for sysadmin role, but not even asingle member for the other roles.

  6. We can also use xp_logininfo to return what accounts are members of a domain group.
    For example: xp_logininfo ‘mycorpdbasupport’, ‘members’;

  7. Hi
    i prefer this to be honest
    select sp.name, sp2.name sp.name, sp2.namefrom sys.server_principals sp inner join sys.server_role_members sr sys.server_principals sp inner join sys.server_role_members sr on sp.principal_id = sr.member_principal_id sp.principal_id = sr.member_principal_idinner join sys.server_principals sp2 on sr.role_principal_id = sp2.principal_id join sys.server_principals sp2 on sr.role_principal_id = sp2.principal_id

    Just use a where clause to filter on types (U, G, S, C, etc)

    • Perry, 

      I couldn’t parse your code. I’d appreciate if you could clarify the t-sql. Thanks!

    • select sp.name, sp2.name
      from sys.server_principals sp
      inner join sys.server_role_members sr on sp.principal_id = sr.member_principal_id
      inner join sys.server_principals sp2 on sr.role_principal_id = sp2.principal_id
      where sp.type in (‘U’, ‘G’, ‘S’, ‘C’)

  8. I got a comment on SQL Server Pedia that I should also include ‘WINDOWS_GROUP’ on the WHERE clause. Thanks!

Leave a Reply

Required fields are marked *.