In order to ease administration, starting with SQL Server 7.0, Microsoft developed several pre-defined fixed server roles. These roles have different permissions and are intended

to provide the ability to spread the administrative responsibilities without

having to give out complete control. DBAs can use these fixed server roles to assign different administrative tasks to personnel and give them only the rights they absolutely need. The server roles available in SQL Server 2000 are:

• bulkadmin
• dbcreator
• diskadmin
• processadmin
• securityadmin
• serveradmin
• setupadmin
• sysadmin

These fixed server roles are very useful if you want to delegate some of the administrative duties of maintaining a particular SQL Server while still maintaining a “tight ship.” For instance, if your organization decides a junior DBA should handle security on its SQL servers but the organization doesn’t want this junior DBA to have complete rights over each system, you can meet this requirement with the use of a server role. You could assign the junior DBA to the securityadmin fixed server role on each server and the junior DBA could carry out his or her security responsibilities without having full administrative rights over the SQL Server

installation. 

Determining Rights of the Server Roles

This example illustrates the power of the fixed server role in SQL Server. To use

these roles properly, I have to understand the rights and limitations of each role. As I discuss these roles, I’ll utilize

a system stored procedure, sp_srvrolepermission, to return what rights the fixed server role has. A caution with

sp_srvrolepermissions: it’s not guaranteed to show all the rights a particular fixed server role has. For instance, consider all the rights the sysadmin account has (everything). Some of the rights require a bit of explanation, more than you’d get with the stored procedure.

There may not be metadata stored in the spt_values table corresponding to these

rights. A good example would be the sp_trace_* set of system stored procedures. These stored procedures are only executable by the sysadmin role. However, when I execute sp_srvrolepermission to get the rights and permissions of the sysadmin role, I don’t see the

sp_trace_* stored procedures although other system stored procedures such as sp_grantlogin and sp_changedbowner are present.

When in doubt, check Books Online. However, sp_srvrolepermissions does still provide a pretty detailed list of the rights for a particular server role – more than enough to be useful. For instance:

 EXEC sp_srvrolepermission securityadmin

returns the following:

Table 1. Permissions for securityadmin role.

 When in doubt, look in Books Online. You should always find the most complete information in SQL Server’s documentation. Speaking of role permissions, let’s now take a look at each fixed server role in detail.

The bulkadmin role

Microsoft added the bulkadmin role so non-administrative users could use the BULK INSERT command. Under SQL Server 7, only members of the sysadmin role were allowed to execute BULK INSERT. In SQL Server 20000, members of the bulkadmin role must still have appropriate rights to the table in question (either the object owner or a member of the db_owner fixed database role). Otherwise, they will receive an

error indicating the user isn’t the database or object owner (Figure 1). 

Figure 1. User assigned to the bulkadmin role without proper rights on the table.

Previously database owners couldn’t use bulk insert to load their own tables. When a BULK INSERT command is executed, the data is loaded straight into the server as an OLE DB row set. It does not go through the network protocols and Microsoft Open Data Services (ODS). In other words, the row set is being created in the memory space

of SQL Server itself. So a failure with BULK INSERT could theoretically affect SQL Server as a whole. That’s why Microsoft didn’t allow anyone other than sysadmins to execute BULK INSERT in SQL Server 7.0.

In SQL Server 2000, Microsoft now gives the option of whether or not to allow BULK INSERT to other logins who don’t need sysadmin rights.

If a login is added to the bulkadmin fixed server role, that login has the ability to use BULK INSERT. This additional role gives the DBA a great deal of flexibility since there is now an option other than the sysadmin role. The DBA can delegate the use of BULK INSERT. Users still have to be the object or database owner, so this role doesn’t give a carte blanche on all tables in all databases.

The dbcreator role

The dbcreator fixed server role has to ability to create, drop, and restore databases. When a database is created, the creator automatically assumes ownership. Therefore, a login with the dbcreator role can initially assign permissions, create objects, and all the other things a db_owner fixed database role user can do for the databases they create (at least, until the ownership is changed). The listed permissions by sp_srvrolepermission

are:

• Add member to dbcreator
• ALTER DATABASE
• CREATE DATABASE
• DROP DATABASE
• Extend database
• RESTORE DATABASE
• RESTORE LOG
• sp_renamedb

Typically, those with sysadmin role permissions handle the creation and management of databases. Their permissions automatically grant them these abilities. Use the dbcreator fixed server role sparingly. Remember, the dbcreator can drop a database just as easily as create one… though if there is a good backup the user has the ability to restore it as well.

The dbcreator role is a good option for junior DBAs who should have the ability to touch databases but not global SQL Server configuration options or external security. A junior DBA assigned to the dbcreator role (and not in the sysadmin role) has the ability to create and maintain databases. However, the junior DBA wouldn’t have the ability to alter who can log in to SQL Server nor would the junior DBA have the ability to change the membership of the sysadmin role. Also, the junior DBA could not alter the server properties that define how SQL Server runs.

Note: Prior to Service Pack 3, members of the dbcreator role could change the ownership of databases they created. However, Service Pack 3 has a lot of security updates and one of them is restricting the ability to change database ownership to the sysadmin role only.

The diskadmin role

The diskadmin fixed server role basically has the ability to add and remove backup devices. The list of rights is rather short:

• Add member to diskadmin
• DISK INIT
• sp_addumpdevice
• sp_diskdefault
• sp_dropdevice

Two of these rights, DISK INIT and sp_diskdefault, are deprecated in SQL Server 2000. Books Online states support is limited in SQL Server 2000 and to “consider replacing references” to DISK INIT with CREATE DATABASE or ALTER DATABASE. The stored procedure sp_diskdefault has much stronger language: “Removed; no longer available. Remove all references to sp_diskdefault.”

Removing DISK INIT and sp_diskdefault, only sp_addumpdevice and sp_dropdevice remain. The diskadmin role can create and delete devices for database backups. However, unless a user receives permissions the database level, the user with diskadmin role rights has no permissions to backup a database by default.

The processadmin role

The processadmin role is another role with rather limited but potent functionality. A user assigned to this role has the ability to issue the KILL command but no other rights. With the KILL command, however, the user with the processadmin role has enormous power since he or she can stop

a query running within SQL Server.

There are a couple of reasons to execute a KILL command. The first would be the case of an orphaned connection. It is entirely possible for an orphaned connection to hold a lock on a resource, preventing other processes from gaining access. The second is if a runaway process occurs. A DBA or user with KILL rights through the processadmin role has to stop the process in order to regain processor and disk I/O resources. Here is an example of a runaway process that would need to be killed:

 CREATE TABLE ATest (

 ATestID int IDENTITY)

 WHILE NOT (1 = 0)

 INSERT ATest DEFAULT VALUES

Since 1 will never equal 0, the process running this query will continue to insert rows until the server runs out of space (whether in the database file or on the disk itself) or the process is killed. Either a processadmin or sysadmin will have to stop the process to prevent the first occurrence. While it is unlikely that a runaway process would be something as simple as this test query, the fact of the matter is this is all it takes. A user could use something like to cause a denial-of-service to the SQL Server.

The securityadmin role

The securityadmin role is used to manage user accounts within SQL Server. Much like the Account Manager group in Windows NT, the securityadmin role has the ability to create (add) and drop logins. Here are the rights granted to the securityadmin role.

• Add member to securityadmin
• Grant/deny/revoke CREATE DATABASE
• Read the error log
• sp_addlinkedsrvlogin
• sp_addlogin
• sp_defaultdb
• sp_defaultlanguage
• sp_denylogin
• sp_droplinkedsrvlogin
• sp_droplogin
• sp_dropremotelogin
• sp_grantlogin
• sp_helplogins
• sp_password
• sp_remoteoption (update)
• sp_revokelogin

The securityadmin role has all the rights needed to allow users to connect to SQL Server. However, the permissions granted to the securityadmin end there. The securityadmin role has no rights to assign database permissions, to include granting users the ability to access a given database.

In order to allow a login in the securityadmin role to give users the ability to access a database,

the login must map to a user in the db_accessadmin fixed database role for the

database. 

The serveradmin role

The serveradmin has the ability to set configuration options using sp_configure as well as shutdown the server. Using sp_srvrolepermission, we get the following list of rights:

• Add member to serveradmin
• dbcc freeproccache
• RECONFIGURE
• SHUTDOWN
• sp_configure
• sp_fulltext_service
• sp_tableoption

Based on the list, the serveradmin role also has the ability to alter Full Text service settings and table options.

There's not a whole lot to say on this one because I would imagine this role

isn't used very often. If a user needs the ability to shutdown SQL Server and

little else, that user is usually doing so through the service (such as a member

of the server team).

The setupadmin role

The setupadmin role is another role that is rather limited. According to Books Online, the setupadmin has the ability to manage linked servers as well as mark stored procedures to be run at startup. The system stored procedure sp_procoption can be used to mark a stored procedure in such a manner. Marked procedures must exist in the master database and belong to dbo. In this script, I mark a stored procedure to be run at startup:

 EXEC sp_procoption 'usp_myStartupProcedure', 'startup', 'true'

This T-SQL code configures the stored procedure usp_myStartupProcedure to be executed each time SQL Server starts. To turn it off, I have to set startup to false:

 EXEC sp_procoption 'usp_myStartupProcedure', 'startup', 'false'

Marking a stored procedure for startup is this simple and sysadmins can write custom stored procedures to carry out certain tasks every time a SQL Server starts up. However only sysadmins can execute this stored procedure successfully.

The original version of Books Online is incorrect where it states the setupadmin role has the ability to mark stored procedures for executing on startup. 

According to Microsoft KnowledgeBase Article 277809, only members of the sysadmin fixed server role are intended to be able to set a stored procedure for startup. The article indicates it was still possible for the setupadmin server role to execute sp_procoption until SQL Server 7.0 Service Pack 3. This was considered a bug because it opens up a huge security hole (of course, no security hole should be considered small). Keep reading for why this is a security hole and what one of the possibilities is. It is not an option for anyone other than those in the sysadmin role in SQL Server 2000. 

If sp_srvrolepermission is executed to see what rights setupadmin has, sp_procoption is listed. The setupadmin role does have the ability to execute this system stored procedure, but the stored procedure will return an error. In SQL Server 2000, sp_procoption has the following code to prevent it from being run by a user unless the user has sysadmin rights:

 -- MUST BE sysadmin (Startup-procs run as sysadmin) -- 

 IF is_srvrolemember('sysadmin') = 0 

 BEGIN 

 raiserror(15247,-1,-1) 

 RETURN @@ERROR 

 END 

If you take a look at the entry in sysmessages corresponding with error 15247, you’ll find the following as the description: “User does not have permission to perform this action.” So even though Books Online (even the updated version from September 2001) versions prior to the one re-issued with the release of SP3 states the setupadmin role has the ability to execute sp_procoption, the role is restricted from doing anything with the stored procedure by the lines of code quoted above. If you or I were to modify the system stored procedure to remove these lines, there are two additional commands in the stored procedure that are not within the rights of the setupadmin role. These two commands are DBCC LockObjectSchema() and RECONFIGURE. Since sp_procoption isn’t available for the setupadmin, the only real right the role has is to manage linked servers. 

Microsoft’s decision to not allow the setupadmin role to set a stored procedure for startup makes a lot of sense, because otherwise a non-sysadmin user could turn on a stored procedure that is run with sysadmin rights when SQL Server started up. Clearly, this is not a “best practice.” However, the original documentation (and the September 2001 update) are both incorrect.

The Books Online version updated for Service Pack 3 has the correct information.

If you haven't upgraded your Books Online version, visit the link I've included

in the Additional Resources section.

If you are still wondering about Microsoft’s decision to only allow the sysadmin role to set a stored procedure for startup, let me cite an example from my own testing that should sway you in favor of Microsoft’s choice.

Another DBA and I were demonstrating the power of sp_procoption on a development SQL Server. I created the following stored procedure and set it to run at startup:

CREATE PROC dbo.usp_GrantAdministratorsAccess

AS

EXEC sp_grantlogin 'BUILTIN\Administrators'

EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sysadmin'

GO

My fellow DBA then removed the BUILTIN\Administrators group from the sysadmin fixed server role. I logged in using a Domain Admin level account (on the OS side, the Domain Admins global group is added to the local Administrators group when a computer joins a domain) and attempted to log on to the SQL Server. I could not. However, since I was a local administrator, I could stop and restart the SQL Server service (MSSQLSERVER). That’s exactly what I did. When SQL Server had restarted I attempted to log on again. Since my stored procedure ran at startup, the BUILTIN\Administrators group was a member of the sysadmin role again. My log on attempt succeeded and had sysadmin level permissions once again. All of this was possible because my stored procedure ran at startup under sysadmin rights. Microsoft restricted the execution of sp_procoption to members of the sysadmin role exclusively to prevent exploits just like what I demonstrated.

The sysadmin role

The sysadmin role, as the name implies, can do anything within SQL Server. It

has complete rights over the SQL Server.. It is the only role that can add others to the sysadmin role. There isn’t anything within SQL Server a sysadmin role member can’t do.

The sysadmin role should be granted sparingly, even on a development server, as it is a super user type of permission (think root on a Unix box). Generally, it's a role reserved for DBAs and possibly system administrators of the server itself. Users in the sysadmin role have complete power over SQL Server. 

Not only that, but a member of the sysadmin role is effectively the same as the Windows account the SQL Server service runs under. A clear example is through the use of xp_cmdshell, an extended stored procedure that gives a sysadmin command-line access on the given SQL Server, except the access is as the service account, not as the DBA’s account. Imagine if a particular DBA didn’t have the ability to access a file using his or her normal account, but the service account SQL Server was running under does. There is nothing (short of dropping xp_cmdshell, and even this isn’t a guaranteed show-stopper

since a sysadmin can add it back) stopping the DBA from using xp_cmdshell to grab that file. 

This leads me into a discussion about how I've seen a security breakdown

involving the sysadmin role. By default, the NT group BUILTIN\Administrators is granted sysadmin rights to SQL Server because it is made a member of the syadmin role. The BUILTIN\Administrators group corresponds to the local Administrators group for the server.

One of the big no-no’s I’ve seen in a development environment is the result of a combination of three things: 

1. BUILTIN\Administrators is left with sysadmin rights
2. Developers are given local Administrator privileges
3. The SQL Server service is running under the same domain account as production systems. 

What this means is the developers have sysadmin rights to SQL Server and if they cross over to another (production) SQL Server, they have rights to that server as well. At the very least, they have the ability to execute xp_cmdshell, giving operating system level functions under the service account’s rights, to the developers. 

If any of the three criteria were not true, developers would not be able to cross over to a production server by means of the production account alone. However, the combination results in a potential security threat to the production environment. Of course, I’m not a big believer in giving developers sysadmin rights to SQL Server or local Administrator rights to a server, even in development. I recognize there are cases where this might be necessary, but generally, I try to avoid the situation. If you give sysadmin rights to a developer, think about not only the server in question, but also other servers that could potentially be accessed by the developer.

One final point about the sysadmin role: it bypasses security checks. Deny permissions don’t stop a sysadmin role as a member of this role completely overrides any security settings. The sysadmin role is truly a super user type of role. Dole it out very carefully.

Concluding Comments

The fixed server roles in SQL Server 2000 are provided for the convenience of

the DBA. These roles break down permissions required to manage a SQL Server

installation to allow a DBA to delegate certain responsibilities without giving

out all the rights to the SQL Server. For instance, a DBA can hand one person

the ability to manage logins while giving another the ability to create and drop

databases. Also, the bulkadmin role gives a user to execute BULK INSERT if the

user owns the table or is marked as a member of the db_owner role for the

database. This is another example of where Microsoft has provided a role to

delegate the responsibilities.

With that said, some of the roles are powerful, as would be expected. Even in

a development environment the roles should be carefully controlled, especially

sysadmin. A development server improperly configured can make for a launching

point against a production server, whether intentional or not. As with anything

regarding security, understand what the options are, consider what is the best

tradeoff for functionality versus security based on the business needs, and use

these roles to your advantage.

Additional Resources

• FIX: A Non-Sysadmin May Execute sp_procoption Stored Procedure (277809) - http://support.microsoft.com/default.aspx?scid=kb;en-us;277809
• SQL Server Books Online Update - http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp