Fixed Server Roles in SQL Server

You can execute the below query and find the fixed and user defined server roles.

SELECT name, principal_id, type, type_desc, is_fixed_role, owning_principal_id 
FROM sys.server_principals WHERE type = 'R'

There are eight types of fixed server roles in SQL Server 2012 as mentioned below. Some part is referred from books online.

1. bulkadmin

If someone has been assigned with bulkadmin fixed server roles then he can run the BULKINSERT statements.

2. dbcreator

If any member is assigned with dbcreator fixed serever role then he can perform database wide operations like create database, drop database, alter database etc. This role is basically given to junior DBAs as you can’t take risk to give sysadmin role to him.

3. diskadmin

A member with diskadmin fixed server role can manage disk files. Practically this role is rarely used in real environment.

4. processadmin

Members with processadmin fixed server roles manages SQL Server processes and can kill running connections.

5. securityadmin

Members with securityadmin roles can reset SQL Server login passwords. They can GRANT, REVOKE and DENY server level and database level permissions and can manage and audit serverlogins.

6. serveradmin

Any members of serveradmin roles can configure server level settings and can also shutdown the server. You can assign this role to junior DBAs if you can’t give sysadmin role to him.

7. setupadmin

Members with setupadmin roles can configures replication and can add or remove linked servers. This role is rarely used as mostly DBAs with sysadmin role performs replication and linked server configuration changes.

8. sysadmin

If any member is assigned with sysadmin fixed server role then he can perform all the server-wide and database-wide activities. This role is very critical and basically assigned to DBAs as sysadmin role can manage and grant any type of access to other members too.