Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Creating a SQL Server 2012 User Defined Server Level Role


By:   |   Last Updated: 2011-06-27   |   Comments   |   Related Tips: 1 | 2 | 3 | 4 | More > 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.

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

execute this t-sql code to verify
Next Steps


Last Updated: 2011-06-27


next webcast button


next tip button



About the author





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

*Name    *Email    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.



    



Learn more about SQL Server tools