List SQL server roles by users

Microsoft SQL Server

SQL Server roles can be used to give users server wide privileges. The server roles are sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator and bulkadmin. The mapping between users and server roles can be found in the system view sys.server_role_members.

Here I’ll show a SQL script that lists all users and their server roles.

The different server roles have the following permissions. From TechNet:


Members of the sysadmin fixed server role can perform any activity in the server.


Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.

Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.


Members of the setupadmin fixed server role can add and remove linked servers by using Transact-SQL statements. (sysadmin membership is needed when using Management Studio.)


Members of the processadmin fixed server role can end processes that are running in an instance of SQL Server.


The diskadmin fixed server role is used for managing disk files.


Members of the dbcreator fixed server role can create, alter, drop, and restore any database.


Members of the bulkadmin fixed server role can run the BULK INSERT statement.

SQL script to list SQL server roles by users
	,MAX(CASE WHEN srm.role_principal_id = 3 THEN 1 END) AS sysadmin
	,MAX(CASE WHEN srm.role_principal_id = 4 THEN 1 END) AS securityadmin
	,MAX(CASE WHEN srm.role_principal_id = 5 THEN 1 END) AS serveradmin
	,MAX(CASE WHEN srm.role_principal_id = 6 THEN 1 END) AS setupadmin
	,MAX(CASE WHEN srm.role_principal_id = 7 THEN 1 END) AS processadmin
	,MAX(CASE WHEN srm.role_principal_id = 8 THEN 1 END) AS diskadmin
	,MAX(CASE WHEN srm.role_principal_id = 9 THEN 1 END) AS dbcreator
	,MAX(CASE WHEN srm.role_principal_id = 10 THEN 1 END) AS bulkadmin
	sys.server_principals AS spR
	sys.server_role_members AS srm
	spR.principal_id = srm.role_principal_id
	sys.server_principals AS spU
	srm.member_principal_id = spU.principal_id
	spR.[type] = 'R'

Here’s what the results look like on my laptop:

SQL Server Roles By User

SQL Server Roles By User

This is the same information (but summarized) that can be seen in Management Studio by double clicking on a Login under Security -> Logins and selecting the Server Roles page:

SSMS Security Logins Server Roles

SSMS Security Logins Server Roles

4 comments » Write a comment

Leave a Reply

Required fields are marked *.