CommitDBA

Database Management Simplified

Ross Group Inc  MemberzPlus  Blogs  |  News  |  Careers  |  Contact    Twitter CommitDBA LinkedIn CommitDBA

CommitDBA Blogs


See What we are Talking About!

CommitDBA Blog


Frank Wright, CommitDBA Senior SQL Server Database Administrator

Server Level Roles in SQL Server 2012

By: Frank Wright, CommitDBA Senior SQL Server Database Administrator

March 15, 2013


Overview

SQL Server handles server level permissions mostly with its predefined fixed server roles. Most privileges that you will have to deal with, or have already dealt with, are at the database level (which will be discussed in a future article). However, in order to get to a SQL database you must first get to the instance (server).

If you have any experience with installing/administering SQL Server, you have probably seen the list of these roles. Since the days of their inception in SQL 7.0 they have remained relatively unchanged. In the late 1990s, when businesses were busy preparing for Y2K, there were plenty of IT personnel on staff. There existed in many IT shops a de-facto standard of separation of duties. The roles, each with a different subset of permissions, were designed with this separation in mind. However, as Y2K passed and IT budgets shrank and as recessions came and went, the IT herds were thinned over the past decade. There were no longer plenty of employees around to split the responsibilities, and usually just one person tasked to handle any specific functional area such as database administration. This, IMO, is why the roles have remained unchanged as there was no driving need from the customer base requesting any enhancements in this area. Now we are in an era of government mandated policies (SOX, HIPAA, etc.) which dictate separation of duties, whereas in the past it was voluntarily imposed. There is one enhancement to server level security contained in the newest SQL 2012 release that I believe was in response to customers’ needs to implement these mandates.

I will discuss all the fixed roles provided by SQL Server including what tasks each is capable of performing (i.e. set of permissions it has) and to whom each should be granted. I will also discuss the new SQL 2012 server level security enhancement. But before I talk about these, I will explain what permissions a login gets by default when it’s created, which happens to be partially implemented by a server role. Basically, a login by default has only the right to connect to SQL which for most regular users this is all they will need at the instance/server level. The tasks they will be required to perform will deal mostly with objects within one or more databases and permissions on those objects are granted to a user in the database which is then linked to a login.

Public Role

In SQL 2000 and prior, there existed only an option for each Windows Authenticated login to be granted (default) or denied the ability to connect and SQL logins were implicitly granted access by their mere existence. Starting in SQL 2005, this is the server role that EVERY login (NT group, AD user, SQL login) belongs to upon creation. Membership in this group is implicit, i.e. not explicitly granted, and therefore not revocable. SSMS will display a pretty little checked box next to this role but it is just for show. Go ahead and try to deselect it (hint: the left button on your mouse is not broken).

Only part of the permissions necessary to connect to SQL are included in this role, otherwise it might be impossible to BLOCK/DENY specific users from connecting (not really but it’s the best reason I could come up with). Only the ENDPOINT security is defined in public. I will talk more in depth about the concept of ENDPOINT security in another article, but to summarize the part we are concerned with deals with protocols used to connect and the purpose of the connection. The second half of the permission set necessary to allow a user to connect is granted directly to the login. While this permission is granted automatically during the CREATE LOGIN command, it is an explicit permission which can be DENIED if and when the time comes for a particular login.

Another interesting peculiarity regarding this role is that it is the only one of the provided roles that is customizable. Server level permissions can be added/revoked to this role, even the ones that are granted by default, when the role is created during installation. If you want to eliminate the NamedPipes protocol from being used on your network, you would start by revoking this ENDPOINT from the public role forcing connections over TCP or other available protocols.

SYSADMIN Role

On the polar opposite side of the spectrum from public we have the sysadmin role. To summarize in one sentence: There isn’t anything in SQL this role can’t do. Not only is it granted permissions to do everything, but it bypasses most security checks altogether making it difficult to prevent a member of this role from accomplishing any task in SQL. It’s because of this that membership in this role should be severely restricted. The default members of this role are the [sa] sql login, the members of the local windows Administrators group, and depending on the version, the service (domain or local) account or correlating group/virtual account created by the installation. Not to get in too deep with securing a SQL system, but for starters you might want to review whether or not server administrators need access to SQL Server and if not remove them from the sysadmin role (but don’t remove the BUILTIN group nor DENY it CONNECT).

Basically the sysadmin role is granted CONTROL SERVER (the UBER SQL server permission) WITH GRANT, which means a member can use or grant to another login any privilege under CONTROL SERVER (i.e. everything). Why not just grant CONTROL SERVER? There are two subtle differences. Without the WITH GRANT, a login would be able to do anything except give those same permissions to another login, and not being a member of sysadmin is not subject to the security check bypass. Most of the permissions available to sysadmin will be documented in one of the following server roles.

DBCREATOR Role

This role allows the login to CREATE, DROP, ALTER, and RESTORE any database. This role fits perfectly into the hands of a JR DBA. It allows this person to perform most of the tasks related to assisting business users & fellow IT staff (i.e. developers) without letting them affect advanced server level settings or server level security. It also fits nicely with granting to the development staff on a non LIVE SQL environment to allow them to create databases as they are needed. Why not grant sysadmin to developers on a non LIVE SQL environment? If developers are allowed to handle server level configurations and security, then you will not be made aware of what was done until close to go-live. You will then be stuck with the hard choice of preventing the go-live of an application or compromising your LIVE SQL environment.

There is one concern with this role if you have heterogeneous environment (i.e. a core SQL installation houses a disparate array of databases with different business and IT user bases). The operative word in the first sentence of the first paragraph of this section is ANY. Granting this role to an IT person to handle their own area of responsibility would allow them to perform the same functions (incl DROP) on any database on the same server. By using the pseudo equivalent server permission CREATE ANY DATABASE, you allow a login to CREATE (and consequently own/control) any database without having implicit permissions to any database they did not create (or own).

SERVERADMIN Role

This role can set/change server level configurations and bounce the instance. It would be rare to see someone granted this permission. Generally, someone needing this privilege would be limited to DBA’s and therefore would already have sysadmin. I would not advise giving this role to JR DBA’s, as server level settings should be left to SR DBA’s. I definitely see value in having a JR DBA or other IT personnel having the limited ability to bounce an instance (especially as an emergency quick fix to a recurring problem) which would have a much quicker turnaround than bouncing the entire server. The ability to do this within SQL has been very limited to date. I would make sure the specified individual has the server level permissions to manage services to be able to bounce and instance. In fact, when using SSMS, the ability to right-click on the instance and choose start/stop/restart has more to do with the windows login under which SSMS is running than the login connected to the instance. SSMS appears to be using Remote Windows Management to control the services rather than issuing SQL commands to perform these actions.

SECURITYADMIN Role

This role can grant/revoke/deny ALL server security settings (except sysadmin membership) and database security for those databases in which the login has the appropriate access (db_accessadmin & db_securityadmin). Depending on how responsibilities are parsed out, this could be an effective role to assign to someone besides the DBA if there is a high frequency of security tasks and the system is not super sensitive. A JR DBA or trusted individual (IT or business) can be tasked with this role, as basic SQL security setups are a routine task, especially via SSMS, and hard for an individual to break something handling the routine onboarding/offboarding tasks. However, I stress the term trusted. Although it’s the business that ultimately decides who should have access, it’s the IT personnel who are the experts in technology and are given the task. If a trusted business user with appropriate level of authority (in the business not the software) can be properly trained, then there is no reason why they couldn’t perform routine security tasks especially when running a skeleton IT staff.

PROCESSADMIN Role

Basically this role can only forcefully end (kill) connections to SQL. There is limited use for this specific role. However, when a fellow IT coworker keeps bothering you with blocking DB connections from the poorly written app he supports, you end up granting him this role so he will leave you alone.

SETUPADMIN Role

Quite possibly the most USELESS role EVER, this role can only manage Linked Servers.

DISKADMIN Role

The second most useless role ever. It has permissions to manage backup devices not database files. Backups devices were significantly used in the past with serial access devices (i.e. tapes). Now most backups are performed disk to disk (local or over the network) or to backup systems that can present themselves as disk devices to the O/S.

BULKADMIN Role

This role allows a login to perform what is known as a BULK INSERT. It is a mass insert mechanism that can defer a lot of the transactional overhead until the end of the load thereby saving significant time and computing resources. The concept of the BULK INSERT and ergo the need for the role were introduced in SQL 2000. This role alone does not allow a login to do anything. The permissions on the target object need to be in place. This is generally not something to be granted to an employee but to service/process logins as part of a scheduled batch job.

USER DEFINED SERVER Roles

The ability to create your own server level roles is a brand new feature in SQL 2012. This feature allows you to custom tailor a restricted set of server permissions for your environment. It does so without having to go without granting a fixed role because it has too many permissions, granting a fixed role and dealing with the aspect that the login will have more privs than necessary, or having to grant the specific server permissions individually to each login.

To create, grant permissions and, if necessary, subsequently remove a custom server role, use the following syntax. In order to create a custom role a login must have been granted the CREATE SERVER ROLE permission or be a member of the sysadmin role.

USE [master]
GO
CREATE SERVER ROLE <role_name> [ AUTHORIZATION <server_principal> ];
GRANT <server permission> TO <role_name>;
DROP SERVER ROLE <role_name>;

e.g. CREATE SERVER ROLE myFirstRole AUTHORIZATION [sa]

Once a role is created you can add members to and remove members from the role. You can also rename the role if you wish. If you have added members to a role, you cannot drop the role until you remove all the members. If you have worked with SQL in previous versions, you might be familiar with handling membership in fixed server roles using sp_addsrvrolemember and sp_dropsrvrolemember. These system stored procedures have been deprecated and replaced with the following more ANSI compliant syntax.

ALTER SERVER ROLE server_role_name

{ [ ADD MEMBER server_principal ]
| [ DROP MEMBER server_principal ]
| [ WITH NAME = new_server_role_name ]};

e.g. ALTER SERVER ROLE myFirstRole ADD MEMBER [mySQLlogin];
e.g. ALTER SERVER ROLE myFirstRole DROP MEMBER [mySQLlogin];

Within the CREATE syntax the AUTHORIZATION clause is the same as creating a custom database role, as it defines the owning principal of the role. In order to change the owner, use the following syntax. To not muddy the ownership chain traversal in SQL server permissions checking I would advise setting the owner to a sysadmin privileged account that won’t ever be removed/demoted (i.e. [sa] or the SQL service account). The members of this role will only be able to perform the actions granted and because you added them to the role you want those actions to be able to succeed.

ALTER AUTHORIZATION ON [ <class_type>:: ] entity_name
TO { SCHEMA OWNER | principal_name };

e.g. ALTER AUTHORIZATION ON SERVER ROLE::[myFirstRole] TO [anotherLogin];

From database assessments to complete database management, CommitDBA can assist your company today. For more information about how CommitDBA can work for you – give us a call at (800) 734-9304.



Got database support needs?
Professional and Affordable DBA and Data Services

Contact us Today