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

 

SQL Server User Defined Server Roles


By:   |   Last Updated: 2012-07-09   |   Comments (6)   |   Related Tips: More > Security

Problem

Ever get tired of assigning server level permissions by adding a login to a predefined server fixed role and then assigning multiple permissions to that login?  In SQL Server 2012 you now have the ability to create a User Defined Server Level Role.

Solution

A new feature to SQL Server 2012 is the ability to create user defined server roles and assign server level/scope permissions to these roles. DBA's have always had the ability to create user defined database roles which act as a security layer at the database level, but we've never been able to create roles at the server level until SQL Server 2012.

In this tip I will show you how to create user defined server roles using T-SQL and SQL Server Management Studio.

What Permissions Can Be Assigned

First, to view the list of permissions that can be assigned to a user defined server role run the following query:

USE master 
GO
SELECT * FROM sys.fn_builtin_permissions(DEFAULT) 
WHERE class_desc IN ('ENDPOINT','LOGIN','SERVER','AVAILABILITY GROUP','SERVER ROLE') 
ORDER BY class_desc, permission_name
GO

Create a Server Role in T-SQL

To create a server role called "juniordba" use the following:

USE master
GO
CREATE SERVER ROLE juniordba

Next we will create a login called Brady and then add it to the new juniordba role that was created:

USE master 
GO
ALTER SERVER ROLE juniordba ADD MEMBER Brady

We haven't added any permissions to the server role, so Brady shouldn't have access. To test this we can login as Brady and run the following query:

SELECT * FROM sys.dm_exec_connections

As you can see we get the following error message:

Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.

Next, I'll assign permissions to the server role that will allow Brady to run DMV's.

GRANT CONNECT SQL TO juniordba 
GRANT VIEW ANY DATABASE TO juniordba 
GRANT VIEW ANY DEFINITION TO juniordba 
GRANT VIEW SERVER STATE to juniordba

After running the query again using login Brady we get the following:

SQL Query

Create a Server Role in SSMS

In SSMS, drilldown into the server and open Security, right click Server Roles and click New Server Role...

Create a Server Role in SSMS

In the New Server Role window, name the server role, choose securables and assign the permissions related to the securables selected. In this example, I have named my server role juniordba, selected Servers as the securable and granted connect to sql, view any database, view any definition, and view server state. I have also denied the shutdown permission.

the New Server Role window

Next, click on the next tab called Members. Here you will add the logins that you want to be associated with the new Server Role. In this example, I'll choose Brady.

click on the next tab called Members

The last tab, Memberships, will allow you to nest the Server Role with default Server Roles. In this example, we wanted to create one from scratch so we'll ignore this and click OK.

In SSMS, you can now see your new Server Role:

In SSMS, you can now see your new Server Role:
Next Steps


Last Updated: 2012-07-09


next webcast button


next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources




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.



    



Thursday, September 11, 2014 - 5:07:53 AM - junior Back To Top

Hi!

What is the best approach in this situation? I have two database, for example, AdventureWorks and ContosoDW, so i should get access for all developers to Person schema in AW, and access to Person schema in Contoso. 


Wednesday, October 24, 2012 - 2:25:54 PM - Walt Beatty Back To Top

THE LANDSCAPE:
NT ACCT = "AUSER"
USER DEFINED SERVER LEVEL ROLE = "F0_GRP" (for financial officer)
DB NAME = "ANY_DB"
TABLE NAME = "TBL_RECORDS"
TSQL = "GRANT SELECT ON TBL_RECORDS TO FO_GRP""

MY QUESTION:
When AUSER queries the table TBL_RECORDS is there a TSQL function that returns the group "FO_GRP" as the function/variable "USER" returns the user, in this case "AUSER"?

 


Wednesday, July 11, 2012 - 1:53:23 AM - SUBHANI SHAIK Back To Top

very helpful.


Tuesday, July 10, 2012 - 9:17:54 AM - Ameena Back To Top

This feature will make our(DBA) lives more easier. Every little bit helps. Thanks for bringing it out in this article to our attention.


Tuesday, July 10, 2012 - 5:30:50 AM - Plahanov Back To Top

Thanks friend. nice explanation.

 

Plahanov


Monday, July 09, 2012 - 6:50:31 PM - TimothyAWiseman Back To Top

Thanks for posting this.  The User Defined Server roles definitely seems like a way to provide more granularity to account permissions and when trying to follow a principle of lowest necessary privileges, granularity is a good thing.


Learn more about SQL Server tools