Limited-Function Server Roles

Server roles debuted in SQL Server 7.0. You use these helpful security tools to assign a predefined set of permissions to one or more database logins. The sysadmin role is the most powerful fixed server role because its members can perform any function on the server. You can use the remaining limited-function fixed server roles, listed below, to grant limited permissions to specific types of users and revoke or reassign permissions as users' job duties change. Remember, a change in a role's permissions applies to all members of the role, and changes to a user's permissions don't take effect until the next time the user logs in.

7. setupadmin

The setupadmin role lets users manage linked servers and alter server startup procedures. Members of this role can also configure replication and install extended stored procedures.

6. dbcreator

This role gives its members permission to create, alter, and drop databases. Members of the dbcreator role can also modify database properties and run the sp_dboption stored procedure.

5. diskadmin

You can assign the diskadmin role to users who need to manage the disk files that SQL Server uses for data and log storage. Members of this role don't have permission to run T-SQL statements, but they can execute the sp_addumpdevice and the sp_dropdevice stored procedures.

4. bulkadmin

The bulkadmin role lets its members execute T-SQL BULK INSERT statements, which are processor-intensive tasks that copy data files into database tables. Systems administrators typically perform bulk inserts, but if users such as data warehouse developers and business intelligence (BI) analysts also need to run BULK INSERT statements, you can assign those users to the bulkadmin role.

3. processadmin

Members of the processadmin role can manage processes running in SQL Server and end jobs outside the SQL Server environment. For example, this role's members can use the T-SQL KILL command to terminate a specified process.

2. serveradmin

You can assign this role to users who need to set server configuration options or users who need to shut down the server. Members of the serveradmin role have permission to execute the sp_configure stored procedure and run the T-SQL SHUTDOWN and RECONFIGURE commands.

1. securityadmin

You can assign the securityadmin role to users who need to create logins and assign CREATE DATABASE permissions. Members of this role can also read SQL Server error logs and change login passwords for all users except those in the sysadmin group.