source: http://www.MSSQLTips.com/tip.asp?id=2424 -- printed: 9/2/2015 4:31:57 AM

Creating a SQL Server 2012 User Defined Server Level Role

Written By: Ashish Kumar Mehta -- 6/27/2011

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
 

Follow

Get Free SQL Tips

Twitter

LinkedIn

Google+

Facebook

Pinterest

RSS

Learning

DBAs

Developers

BI Professionals

Careers

Q and A

Today's Tip

Resources

Tutorials

Webcasts

Whitepapers

Tools

Search

Tip Categories

Search By TipID

Authors

Community

First Timer?

Pictures

Free T-shirt

Contribute

Events

User Groups

Author of the Year

More Info

Join

About

Copyright

Privacy

Disclaimer

Feedback

Advertise

Copyright (c) 2006-2015 Edgewood Solutions, LLC All rights reserved
Some names and products listed are the registered trademarks of their respective owners.