Creating a SQL Server Denali User Defined Server Level Role

By: Ashish Kumar Mehta | Read Comments | Print Ashish has been contributing to the MSSQLTips.com community since 2009 with over 60 tips. Related Tips: 1 | 2 | 3 | 4 | More

Problem

While looking through the new features and improvements in SQL Server Denali, 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 Denali, 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 Denali.

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 Denali User Defined Server Role using SQL Server Management Studio

1. Using SQL Server Management Studio, connect to SQL Server Denali 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 Denali.

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

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

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

• Download Community Technology Preview CTP1 of SQL Server Denali
• Learn more about Server-Level Roles in SQL Server Denali
• Stay tuned for more tips on SQL Server Denali

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code