New User-defined Server Roles in SQL Server 2012

Introduction

As the infrastructure grows to sustain the growth of an enterprise so does the number of DBAs to support this infrastructure. Different groups of DBAs perform separate sets of roles and responsibilities; for example there can be one group of DBAs responsible for the overall management of SQL instances and there cam be another group of DBAs responsible for performance optimization and improvements. This later group of DBAs does not need permission to control the whole instance but rather they only need permissions to query DMVs, run traces/profiler, etc.

Now as we all know, we have fixed server roles (total nine) in SQL Server and if we later make a group of DBAs a member of sysadmin, so that they can query DMVs and run traces/profile, we are actually giving them more than the permissions needed. Being part of sysadmin, they will have complete control on the instance. Unfortunately there is no way to change these fixed server roles to limit/give more permissions than needed in earlier versions of SQL Server. This means there is no way to separate DBAs into groups according to their roles, other than those that are available as fixed server roles.

Starting with SQL Server 2012, apart from using the fixed server roles, we can also create user defined server roles and assign only server level/scope permissions needed to this role.

User Defined Server Roles in SQL Server 2012

SQL Server 2012 brings a couple of new security enhancements, one of which is to create a user defined server role. We can create user defined server role either using the SSMS (SQL Server Management Studio) or by writing a TSQL command (CREATE SERVER ROLE, ALTER SERVER ROLE and DROP SERVER ROLE).

Whatever approach we take to create a user defined server role, there are three steps involved:

  • Create user defined server role
  • Add member to this user defined server
  • Assign appropriate server scope permissions

SQL Server also allows you to create these user defined roles in hierarchical manner as well, which means one user defined server role can belong to another user defined server role and so on.

You can run the query below to see a list of permissions that can be assigned to user defined server roles:

USE master
SELECT * FROM sys.fn_builtin_permissions(DEFAULT) 
WHERE class_desc IN ('ENDPOINT','LOGIN','SERVER','AVAILABILITY GROUP','SERVER ROLE') 
ORDER BY class_desc, permission_name
GO

Creating Defined Server Roles in SQL Server 2012

As I said before, there are two ways to create a user defined server role, either by using TSQL commands or by using the SSMS user interface (there is third way as well, using the PowerShell command but that is not in the scope of this article).

The TSQL code given below creates a user defined server role, creates a login and then adds that login to the user defined server role that was created. You use ALTER SERVER ROLE…ADD MEMBER… command to add a member to the server role and ALTER SERVER ROLE…DROP MEMBER… command to remove a member from the server role:

USE master
--Creating a user defined server role
CREATE SERVER ROLE [DBAExtendedRole]
-- Create login [Arshad]
CREATE LOGIN [Arshad] WITH PASSWORD = 'asdf~1234'
-- Add [Arshad] to [DBAExtendedRole] user defined role
ALTER SERVER ROLE [DBAExtendedRole] ADD   MEMBER Arshad
GO

If you try to query a DMV (which needs view server state permission) under the credentials of the login we created above (you can either use the login to connect to the server or use impersonation as shown below to work under the security context of the login we created), it will fail because even though we have added the login to the server role, we have not given any permission so far to this user defined server role:

SELECT SUSER_SNAME()
EXECUTE   AS LOGIN = 'Arshad'
SELECT SUSER_SNAME()
SELECT * FROM sys.dm_exec_connections
REVERT
SELECT SUSER_SNAME()

Figure 1 - Result 1
Figure 1 – Result 1

Figure 2 - Result 2 
Figure 2 – Result 2

So what we need to do, is to assign appropriate permissions, as shown below, to the server role that we created so that members of the server roles can work on performance optimization on the server by querying DMVs or running traces/profiler:

--Assign permissions to user defined role
GRANT CONNECT SQL TO [DBAExtendedRole]   
GRANT VIEW ANY DATABASE TO [DBAExtendedRole] 
GRANT VIEW ANY DEFINITION TO [DBAExtendedRole] 
GRANT VIEW SERVER STATE TO [DBAExtendedRole] 
GRANT ALTER TRACE TO [DBAExtendedRole]   

Now as we have given appropriate permissions to the server role, let’s run the same DMV query as above under the security context of the login that we created and this time you will see it returns data as expected:

SELECT SUSER_SNAME()
EXECUTE   AS LOGIN = 'Arshad'
SELECT SUSER_SNAME()
SELECT * FROM sys.dm_exec_connections
REVERT
SELECT SUSER_SNAME()

Figure 3 - Result 3
Figure 3 – Result 3

Creating a user defined server role using the SSMS is quite easy. Right click on the Server Roles node under the Security node and click on New Server Role as shown below:

Figure 4 - Create New Server Role
Figure 4 – Create New Server Role

In the New Server Role creation wizard, you need to first specify the name of the server role and then select securable and its associated permissions. You can select/specify GRANT, WITH GRANT or DENY for each permission of the selected securable as shown below:

Figure 5 - Specify the Name of the Server Role
Figure 5 – Specify the Name of the Server Role

Once you are done with creating the server role and assigning permissions to it, next you need to add members in this server role. Click on the Members on the left to open up page to add or remove members in the server role as shown below:

Figure 6 - Add or remove members to the Server Role
Figure 6 – Add or remove members to the Server Role

As I said before, server roles can be nested and can be created in an hierarchical way. Click on the Memberships on the left to open up the membership selection page as shown below, check the appropriate server role to make this user defined server role a member of the selected server roles.

Figure 7 - Server Role memberships
Figure 7 – Server Role memberships

Conclusion

SQL Server 2012 brings a couple of new security enhancements and one of them is to create user defined server roles; a really nice feature, which simplifies instance wide administration and helps in increasing the security of the instance by letting you define different groups of persons with different sets of permissions as per their role and responsibilities.

Resources

What’s New (Database Engine)

Programmability Enhancements (Database Engine)

Server Roles

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles