 |
|
Understanding SQL Server fixed server roles
|
By: K. Brian Kelley | Read Comments (2) | Print Brian is a SQL Server author and columnist focusing primarily on SQL Server security. Related Tips: More |
|
Problem
I know there are fixed server roles that come with SQL Server. How do I best use them within my installations? What should I watch out for?
Solution
SQL Server does indeed come with several built in fixed server roles. They are:
- sysadmin
- bulkadmin
- dbcreator
- diskadmin
- processadmin
- securityadmin
- serveradmin
- setupadmin
Let's look at each in turn.
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. As a result, membership in this role should be carefully controlled. In typical production environments you would only see DBAs being a member of this role. In very strict environments, you'll even see it restricted so that only particular DBAs within an organization have this level of access.
Because of the special nature of this role, let me also warn you that there is some inconsistency in how it is reported on with respect to other roles. Because the sysadmin role can do anything within the SQL Server, it has all the rights of the other roles and more. And that can make how you report on it a bit difficult to handle. You want to indicate what rights it has, but you also want to be very specific about who is in what server role. Therefore, you just have to know how the different structures report the information back. For instance, if you use the system function, IS_SRVROLEMEMBER(), and you specify a role other than sysadmin for a login that's a member of the sysadmin role, you'll get a "1" back, indicating that the login is a member. For instance:
SELECT IS_SRVROLEMEMBER('bulkadmin');
However, if you check the membership using the sp_helpsrvrolemember system stored procedure, you won't get the same results. The login won't be shown as a member of the bulkadmin role. Also, if you query the sys.server_role_members catalog view, you also won't see it reported as a member of the bulkadmin role (or any other role, unless you've explicitly granted such).
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. So granting access to this role doesn't mean carte blanche ability to BULK INSERT into any table a user has access to. But typically, I've only granted this role to service accounts that are performing Extract, Transform, and Load (ETL) type of operations, such as moving data from a source system into a data warehouse.
- 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. Again, this is typically not something a normal user is a member of. 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. The junior DBA has the ability to create databases in preparation for development teams and the like. I've also seen 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. That's an important thing to remember if you don't have some automated way of changing the owner automatically after the fact. The owner of a given database can do anything within that particular database. Therefore, that needs to be taken into consideration when you grant membership to this role.
- 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. As a result, there's not much point in giving this out. So you should almost never see this used.
- 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. However, because it can terminate a connection to SQL Server, it's a powerful server role. Therefore, if you do use it, make sure the logins which are members of this role are trustworthy. 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. Therefore, membership in this role should be carefully controlled. This is another role you might see be given to junior DBAs where sysadmin rights aren't appropriate.
- 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
Another role whose name should shout, "Danger!" And indeed this role does shout that for it, too, has very privileged permissions. 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. There are occasions where you might want junior DBAs to have this level of access, but typically you want your SQL Server configurations standardized and changed rarely, and even then, only intentionally. So you'd typically see senior DBAs with such access.
- 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.
- Setup admin basically gives control over linked servers.
- Again, this is not a role I've seen used in practice.
- I would limit this sort of configuration change to DBAs who understand the impact, meaning they are already members of the sysadmin role (meaning you don't need this role).
Next Steps
- Understand the security implications before assigning server roles
- Find more SQL Server related security tips here
Last Update: 11/24/2009
| Share: |  |
Share | Tweet |
|
 |
 |
Connect with MSSQLTips.com |
Comments and Feedback:
| Thursday, March 15, 2012 - 7:07:36 AM - Marten Rune | Read The Tip |
|
Id like to add a comment about the DBCreator role. It can create any database, users can not view information in other databases, this is all fine. The problem with this role is that it can DROP ANYDATABASE, even databases where it has no access. I usally recommend the right CREATE ANY DATABASE instead of this role. If CREATE ANY Database is granted, user can create database AND drop databases where the user is a DBO, i e databases created by this user. So to be careful, see if CREATE ANY DATABASE does the trick, use this instead. If you want to lock down further you can add: I have a script I use to add install users in a fairly locked down way: USE MASTER USE [msdb] Press CTRL+M and fill in username and password of your choice. Regards Marten |
|
| Thursday, March 15, 2012 - 7:20:18 AM - Marten Rune | Read The Tip |
|
Great blogarticle! To contribute Id like to add a comment about the DBCreator role. It can create any database, users can not view information in other databases, this is all fine. The problem with this role is that it can DROP ANYDATABASE, even databases where it has no access. I usally recommend the right CREATE ANY DATABASE instead of this role. If CREATE ANY Database is granted, user can create database AND drop databases where the user is a DBO, i e databases created by this user. So to be careful, see if CREATE ANY DATABASE does the trick, use this instead. If you want to lock down further you can add: I have a script I use to add install users in a fairly locked down way: USE MASTER USE [msdb] Press CTRL+M and fill in username and password of your choice. Regards Marten (www.prosql.se) |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |