Limited-Function Server Roles

Here's a quick introduction to fixed server roles in SQL Server 2000 and 7.0.

Michael Otey

September 24, 2002

2 Min Read
ITPro Today logo in a gray background | ITPro Today

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.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like