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