Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Understanding SQL Server fixed server roles


By:   |   Read Comments (8)   |   Related Tips: More > Security

Attend a SQL Server Conference for FREE >> click to learn 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:


signup button

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, July 29, 2015 - 2:00:50 PM - K. Brian Kelley Back To Top

If you are already sysadmin, the only reason you would need any of the other permissions is if a 3rd party application explicitly looked for membership in the alternate role. I've seen those.

If someone doesn't understand that sysadmin is root, administrator, "God" over SQL Server and doesn't need the additional assignments, that's a good sign said person shouldn't have such access. Just sayin'.


Wednesday, July 29, 2015 - 12:58:22 PM - JOHN_DBA Back To Top

Hi Guys,

 

I have been trying to explain to a business owner (non technical person) the different roles within a SQL server. There are several issues at hand, but I will get to the core of the issue. We have a consulting company which has proven time after time their lack of knowledge on SQL or any technology for that matter. The consultant keeps on insisting that the account in question needs ALL permissions and when I say ALL permissions I mean ALL. I already noticed that this person has no clue of what theyre talking about but for my piece of mind, can someone explain in what scenario you would EVER use all these permissions/roles if youre already a sysadmin?

Please explain because I am exhausted already and maybe a 3rd party's input can provide some light in this issue. 

 

db_backupoperator;

db_datareader;

db_datawriter;

db_ddladmin;

db_denydatareader;

db_denydatawriter;

db_owner;

db_securityadmin;

public;

System Administrators;

Security Administrators;

Server Administrators;

Setup Administrators;

Process Administrators;

Disk Administrators;

Database Creators;

Bulk Insert;

Administrators”

“User/Role;

 

Provide the following permissions to SQL database:

 

Create Table;

Create View;

Create SP;

Create Default;

Create Rule;

Create Function;

Backup DB;

Backup Log”

 

Monday, May 18, 2015 - 4:56:12 AM - Roopali Back To Top

Nice post.. very helpful..


Saturday, August 30, 2014 - 8:02:23 AM - Rekha Shah Back To Top

Dear Sir,

 

I am new users of SQL Server.  We  wants  to restrict  permission to other users  -  from back end  query  UPDATE, DELETE, INSERT, ALTER,CREATE etc.  which can change data  will  not work.  only select query will fire for particular database with particular period.

From front end software  restrict  it.

Please guide us  how  we can do this


Tuesday, December 11, 2012 - 4:14:41 AM - Dinesh Back To Top

I want all login details per datbases.


Wednesday, October 24, 2012 - 4:22:07 AM - Ondrej Liptak Back To Top

Thank you for article, well to got handy it.

But let me one question to my problem. I got server with many logins in that has access to their databases. but now there is one login, that would like to restore its dedicated db. Login's got dbcreator role and public. so normally it cant assess to other DBs. that is ok. but after restore its DB, user found, that he can't access under dedicated dbcreator role login restored db although login became DB's owner. I expected, that once login become owner, it will be possible to acces restored DB. It is not.

Any tips how to understand this? or how to solve it ideally to enable login do it under its scope and not able him to access any DBs

Sincerely,
Ondrej


Thursday, March 15, 2012 - 7:20:18 AM - Marten Rune Back To Top

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.
Its enabled i n T-SQL by:
use [master]
GO
GRANT CREATE ANY DATABASE TO [UserName]
GO

If you want to lock down further you can add:
DENY VIEW any DATABASE, then the user can olny see his/hers databases. And in addition if Jobs are needed for the user,
grant the SQLAgentUserRole.

I have a script I use to add install users in a fairly locked down way:

USE MASTER
GO
CREATE LOGIN [<UserName,sysname,>] WITH PASSWORD=N'<password,nVarchar(MAX),>>', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
DENY VIEW any DATABASE to [<UserName,sysname,>]
GO
GRANT CREATE ANY DATABASE TO [<UserName,sysname,>]
GO

USE [msdb]
GO
CREATE USER [<UserName,sysname,>] FOR LOGIN [<UserName,sysname,>] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N'<UserName,sysname,>'
GO

Press CTRL+M and fill in username and password of your choice.

Regards Marten (www.prosql.se)
(This second post was to get the CTRL+M links to work, html did not like them so I pasted html code for them instead)


Thursday, March 15, 2012 - 7:07:36 AM - Marten Rune Back To Top

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.
Its enabled i n T-SQL by:
use [master]
GO
GRANT CREATE ANY DATABASE TO []
GO

If you want to lock down further you can add:
DENY VIEW any DATABASE, then the user can olny see his/hers databases. And in addition if Jobs are needed for the user,
grant the SQLAgentUserRole.

I have a script I use to add install users in a fairly locked down way:

USE MASTER
GO
CREATE LOGIN [] WITH PASSWORD=N'', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
DENY VIEW any DATABASE to []
GO
GRANT CREATE ANY DATABASE TO []
GO

USE [msdb]
GO
CREATE USER [] FOR LOGIN [] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'SQLAgentUserRole', N''
GO

Press CTRL+M and fill in username and password of your choice.

Regards Marten


Learn more about SQL Server tools