Fixed server roles

SQL Server come with several built in fixed server roles. They are:

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

Sysadmin

I start with sysadmin out of order because it is the most important of all the server roles. Quite simply, if a login is a member of this role, it can do anything within the SQL Server. Moreover, it bypasses all security checks. So even if you are able to set up a scenario that should block a login from doing something, by nature of the login being a member of this role, it will ignore that restriction. In typical production environments you would only see DBAs being a member of this role.

Some things to take away with respect to this role (and we'll do this after each role):

• This role can do anything within SQL Server.
• This role completely bypasses all security checks.
• This role can do everything any other role can do and more.
• This is the most powerful role in SQL Server. Limit its access to only those who absolutely need it.

Bulkadmin

Bulk insert operations means taking data out of files and putting them into database tables and that means interacting with elements outside of SQL Server itself. As a result, SQL Server has broken out the ability to carry out bulk insert operations to ensure you only allow it when you intend to. And that's what this role does. Typically normal users do not need to be a member of this role. Only if they have to execute a BULK INSERT in their user context would they need such permission. If users are doing bulk insert operations, they need to be more than just a member of the bulkadmin role. They also need INSERT rights on the table(s) in question.

• This role allows the import of data from external files.
• Typically, this role isn't needed by normal users.
• Even being a member of this role isn't enough. You also have to have INSERT rights on the table.
• Members of the sysadmin role don't need to be a member of this role. Only use it when you have users or logins who need to do BULK INSERT operations who aren't already members of sysadmin.

DBcreator

As the name implies, this role allows a login the ability to create databases. In environments where you might have junior DBAs who aren't given full sysadmin rights, this is typically one of the roles which are used. In cases where a given application rolls over data into new databases on a periodic basis. In order to do so without intervention, the login the application is using needs the ability to create the new database. And that's an example where dbcreator comes in handy. One other point I'll make is that when a database is created, the owner of the database is the one who created it.

• This role allows creation of databases within SQL Server.
• This is another role that should be rarely used.
• It is an ideal role for a junior DBA to give him/her some control over SQL Server, but not the level of permission sysadmin grants.
• Some applications will need to be a member of this role if they "roll over" databases as part of their operations.

Diskadmin

This is a role that typically sees very little use in most environments. It has the ability to manage specified backup devices, but not much else.

• This role allows management of backup devices, which aren't used very much in SQL Server any more.
• I have never seen this role used in practice because backup jobs are typically automated (or should be).

Processadmin

The processadmin server role has the ability to alter any connection. That basically means it can disconnect folks from SQL Server. Because it doesn't have much more than this capability, it's also a server role that's typically not used. Someone with this role could effectively create a denial of service attack on your SQL Server by terminating connections as they come in.

• This is a powerful role because it can kill connections to SQL Server.
• This is another role that should be rarely used.
• I have never seen this role used in practice because typically if connections are to be killed off, you want a fully trained DBA handling this, who are typically members of the sysadmin role.

SecurityAdmin

As the name implies, this server role controls security for the SQL Server... to a point. This role allows a login to manage logins to SQL Server as far as granting/revoking, enabling/disabling, and determining what databases logins have access to. But once you cross that database boundary, a login without additional rights on the specific database can't manage permissions within the database. However, due to the fact that it can manage logins, it is a powerful role. I should also point out that a member of securityadmin can manage the other server roles with the exception of the sysadmin role.

• This role controls logins for SQL Server.
• This role can grant access to databases within SQL Server.
• This role, by itself, cannot define any further security within a database.
• This is another good role for junior DBAs when sysadmin is too much.
• Because it is a security related role, membership in it should be carefully restricted, especially in production.

Serveradmin

A member of serveradmin can control the SQL Server configuration and even shutdown SQL Server by issuing the SHUTDOWN command if connected. Because of this, you should rarely see anyone be a member of this role. Typically DBAs control SQL Server configuration and they are usually in the sysadmin fixed server role, which already has such permissions.

• This role manages the SQL Server configuration.
• This is another role I've not seen used in practice very much because typically you want DBAs who are members of sysadmin handing configuration.
• This is possibly a role you would hand to a junior DBA, but I wouldn't, especially on a production system.

Setupadmin

Setupadmin can control linked servers. Again, this is a role you don't see much use of. If you have the need to create linked servers, this typically falls to the DBAs who already have such permissions through the sysadmin role. Therefore, this is another role you check primarily to make sure no one has this level of access.