Fixed Server Roles in SQL Server

To maintain security and access level of database, SQL Server provides fixed server roles. As the name indicates, you cannot modify or alter server roles hence it helps to manage permissions on the server. Permissions granted to fixed server roles cannot be changed as it is fixed and predefined internally. You need to assign fixed server role very carefully to manage and secure your database.

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'

FixedRole

You can also check the fixed server role by executing below query.

EXEC sp_helpsrvrole

FixedRole2

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 BULK INSERT 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 server logins.

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.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like...

3 Responses

  1. jagdeepsangwan says:

    Hi Manzoor Siddiqui,

    Which version to check about this?

    Regards,
    Jagdeep Sangwan

  2. Typically DBAs control SQL Server configuration and they are usually in the sysadmin fixed server role, which already has such permissions. There are occasions where you might want junior DBAs to have this level of access, but typically you want your SQL Server configurations standardized and changed rarely, and even then, only intentionally.

Leave a Reply

Your email address will not be published.