Creating a SQL Server 2012 User Defined Server Level Role

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


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.


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]

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.

sql server denali user defined server role

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.

ssms new server role

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.

new server roles-levelonedbas

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.

make the current used defined server role a member of another server role

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]

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'
/* Since you don't have the permission to shutdown the instance the script should fail */
/* 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

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

execute this t-sql code to verify
Next Steps

Last Updated: 2011-06-27

get scripts

next tip button

About the author

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Recommended Reading

New Facets and Properties in SQL Server 2012

Configuring Server Startup Parameters in SQL Server 2012

SQL Server 2012 Contained Database Feature

Register a SPN for SQL Server Authentication with Kerberos

Storing passwords in SQL Server things to know to keep the data secure

get free sql tips
agree to terms

Learn more about SQL Server tools