Blog

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:

sysadmin

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

securityadmin

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.

[red_box]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.[/red_box]

serveradmin

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

setupadmin

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.)

processadmin

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

diskadmin

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

dbcreator

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

bulkadmin

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

SQL script to list SQL server roles by users

SELECT
	spU.name
	,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
FROM
	sys.server_principals AS spR
JOIN
	sys.server_role_members AS srm
ON
	spR.principal_id = srm.role_principal_id
JOIN
	sys.server_principals AS spU
ON
	srm.member_principal_id = spU.principal_id
WHERE
	spR.[type] = 'R'
GROUP BY
	spU.name

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

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: