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.
[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]
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
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: