How to get all the users on a database with SA access?

Posted on

Question :

I am new to SQL database and besides running basic queries I don’t know much. Trying to figure out how to get users with SA access.

Can anyone guide me through this, will really appreciate the help.

Answer :

The simplest scenario (a user explicitly granted sysadmin directly) is covered by the following query:

SELECT p.name
  FROM sys.server_principals AS p
  INNER JOIN sys.server_role_members AS m
  ON p.principal_id = m.member_principal_id
  WHERE m.role_principal_id = 3;

But then you may also have users who are members of a Windows group (which SQL Server won’t know about) and that group has been granted sysadmin. To get all of those users you may need to run some queries against Active Directory (or ask someone who can).

Nobody suggested IS_SRVROLEMEMBER, so here’s a third option. Same aspects applies as what Aaron mentioned.

SELECT * FROM sys.server_principals AS p WHERE IS_SRVROLEMEMBER('sysadmin', p.name) = 1

Following query will give the logins and windows groups with sa permissions on a server.

SELECT name,isntname, isntgroup, isntuser
  FROM master.sys.syslogins 
  WHERE sysadmin = 1 
  ORDER BY isntgroup, isntname, isntuser

Result set points out the login as windows groups or windows logins as well.

Leave a Reply

Your email address will not be published. Required fields are marked *