Understanding SQL Server fixed server roles


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

  1. sysadmin 
  2. bulkadmin 
  3. dbcreator 
  4. diskadmin 
  5. processadmin 
  6. securityadmin 
  7. serveradmin 
  8. setupadmin 


Quick query to find roles:


SELECT IS_SRVROLEMEMBER('bulkadmin'); -- Confirms yes or no for specified role for current user 
sp_helpsrvrolemember -- Returns information about the members of a SQL Server fixed server
Let's look at each in turn.
________________________________________
Sysadmin: sysadmin  is the most important of all the server roles. Quite simply, if a login is a member of this role, 

  1. It can do anything within the SQL Server. 
  2. It bypasses all security checks. 
  3. 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. 
  4. Membership in this role should be carefully controlled. This is the most powerful role in SQL Server. Limit its access to only those who absolutely need it. 
  5. In typical production environments you would only see DBAs being a member of this role. 
  6. In very strict environments, you'll even see it restricted so that only particular DBAs within an organization have this level of access.

________________________________________
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. 

  1. This role allows the import of data from external files. 
  2. Typically, this role isn't needed by normal users (Only if they have to execute a BULK INSERT). 
  3. Even being a member of this role isn't enough. You also have to have INSERT rights on the table. 
  4. 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. 
  5. Grant 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.

________________________________________
DBcreator: This role allows a login the ability to create databases.  In SharePoint applications, 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.

  1. This role allows creation of databases within SQL Server. 
  2. This is another role that should be rarely used. 
  3. 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. 
  4. Some applications will need to be a member of this role if they "roll over" databases as part of their operations. 

________________________________________
Diskadmin: I never found use of this role defined any where with example. Msdn says “The diskadmin fixed server role is used for managing disk files.” which is quiet generic 

What I found online is : 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.

  1. This role allows management of backup devices, which aren't used very much in SQL Server any more. 
  2. 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. This is a powerful role because it can kill connections to SQL Server. 

  1. 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. 
  2.  In companies which follows process religiously and having different operators for managing different DBA activities, processadmin will be very helpful role
  3. Someone with this role could effectively create a denial of service attack on your SQL Server by terminating connections as they come in.

________________________________________
SecurityAdmin: As the name implies, this server role controls security for the SQL Server... to a point. 

  1. This role controls logins for SQL Server. 
  2. 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.
  3. This role can grant access to databases within SQL Server but  by itself, cannot define any further security within a database. 
  4. This is another good role for junior DBAs when sysadmin is too much. 
  5. 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.  

  1. This role manages the SQL Server configuration. 
  2. This is another role I've not seen used in practice very much .
  3. Typically DBAs control SQL Server configuration and they are usually in the sysadmin fixed server role, which already has such permissions. 
  4. This is possibly a role you would hand to a junior DBA, but I wouldn't, especially on a production system. 

________________________________________
Setupadmin: This controls linked servers.   

  1. Setup admin basically gives control over linked servers. 
  2. Again, this is not a role I've seen used in practice until you have separate team for automation or application team want to close or open linked server connections. 
  3. 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). 


Must Read: http://www.mssqltips.com/sql-server-tip-category/19/security/


1 comment:

Subscribe to: Post Comments (Atom)