Creating a SQL Server 2012 User Defined Server Level Role

By: Ashish Kumar Mehta   |   Updated: 2011-06-27   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Security

Problem

While looking through the new features and improvements in SQL Server 2012, we found a potentially interesting feature which can be used to Create a User Defined Server Level Role. This is a very interesting feature as this helps DBAs to Create a User Defined Server Role and even add Server Level Permission to the User Defined Server Roles as per their requirements. In this tip, we will take a look at an example where I will be creating a User Defined Server Role which will be used by Junior SQL Server Support Team members to monitor server health.

Solution

There was always a limitation in the previous versions of SQL Server to create a User Defined Server Level Role. However, beginning SQL Server 2012, you can create user defined server roles and add server level permissions to the user defined server roles. Let us go through an example which demonstrates how to create a user defined Server Role in SQL Server 2012.

Create a SQL Server Login

1. Create a SQL Server Login using the below mentioned TSQL script. This login will be used by Junior SQL Server Support Team Members to monitor SQL Server on a regular basis.

USE [master]
GO
CREATE LOGIN [L1DBAs] 
 WITH PASSWORD=N'L1$upp0rtDBAs', 
 DEFAULT_DATABASE=[master],
 CHECK_EXPIRATION=OFF, 
 CHECK_POLICY=OFF
GO

Once the SQL Server login is created successfully the next step will be to create a new Server Role.

Create a SQL Server 2012 User Defined Server Role using SQL Server Management Studio

1. Using SQL Server Management Studio, connect to SQL Server 2012 Instance and open Object Explorer and expand the Security folder.

2. Right click the Server Roles folder and then select New Server Role... from the drop down list as shown in the screen shot below.

3. In the General Page of New Server Role window, enter a name for the new server role in the Server Role Name box.

4. In the Owner box, mention the name of the Server Principal which will own the new role you are creating.

5. In the Securable box, you need to select one or more server level securable as per your requirement. Once you have selected a securable, this server role can be then be granted or denied permissions on that securable.

6. In the Explicit Permissions box, you can select the check box to Grant, With Grant or Deny Permission to this Server Role for the selected Securable. In this example, permission is granted for View any database and View server state as shown in the screen shot below.

7. In the Members Page, using the Add button, add the appropriate logins that will represent individuals or groups to this New Server Role. In this example, we will add the newly created login namely L1DBAs as shown in the image below.

8. In the Memberships Page, you can select the appropriate check box to make the current User Defined server role a member of another server role. In this example, we will leave the settings as default shown in the screen shot below.

9. Finally, click OK to create a New Server Role in SQL Server 2012.

Create a SQL Server 2012 User Defined Server Role using T-SQL

Execute the below mentioned TSQL script to create a Server Role in SQL Server 2012.

USE [master]
GO
CREATE SERVER ROLE [LevelOneDBAs] AUTHORIZATION [sa]
GO
ALTER SERVER ROLE [LevelOneDBAs] ADD MEMBER [L1DBAs]
GO
GRANT VIEW ANY DATABASE TO [LevelOneDBAs]
GO
GRANT VIEW SERVER STATE TO [LevelOneDBAs]
GO

Verify Permissions Assigned to the Newly Create Server Role

You can verify the permission assigned to the newly created server role by executing the T-SQL code below.

/* Since you don't have permission to backup database the script should fail */
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'C:\AdventureWorks2008R2.BAK'
GO
/* Since you don't have the permission to shutdown the instance the script should fail */
SHUTDOWN
GO
/* Since you have VIEW SERVER STATE PERMISSION you will be able to see the results */
SELECT * FROM SYS.dm_exec_requests WHERE session_id > 50
GO

In the screen shot below you can see that using the L1DBAs login, the user is unable to perform the database backup and was unable to shutdown the SQL Server Instance. However, since the user has the VIEW SERVER STATE permissions the user was able to view results from the Dynamic Management Views (DMVs).

Next Steps

• Learn more about Server-Level Roles in SQL Server 2012
• Stay tuned for more tips on SQL Server 2012

About the author

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips