By: Brady Upton | Comments (7) | Related: > 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:
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:
Create a Server Role in SSMS
In SSMS, drilldown into the server and open Security, right click Server Roles and click New Server Role...
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.
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.
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:
Next Steps
- Read more SQL Server Securty tips
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
