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'
You can also check the fixed server role by executing below query.
EXEC sp_helpsrvrole
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
