User-Defined Server Roles - new server option in SQL Server 2012

  • Article

Along with Organization growth and databases growth we need more specific permissions for DBAs, operations and development teams.

Starting SQL Server 2005 we can grant server permissions on a more granular level, for example “view server state” and “alter trace”.
In SQL Server 2005 and 2008 we are able to grant a new server level permission to specific logins in SQL Server.

Along with the growth of members in each team, the maintenance at SQL Server level became more complicated.
In SQL Server 2012 we got a new option to create “User-Defined Server Roles” and assign relevant logins to the newly created role.
I find it very useful, as a lot of my customers have operations teams that need to view and terminate sessions in case of blocking and use profiler for analysis of production issues.
Now we can create server role for this purposes.
 
Here short example of T-SQL code:

 

 -- creation of User-Defined Server Roles 
 create server role UDSR_Operations_Team 
 GO 
   
 -- assigning newly created role to existing fixed role 
 alter server role processadmin add member UDSR_Operations_Team 
 GO 
   
 -- granting permissions to User-Defined Server Roles 
 grant view server state to UDSR_Operations_Team 
 GO 
 grant alter trace to UDSR_Operations_Team 
 GO 
   
 -- assigning SQL login to User-Defined Server Roles 
 alter server role UDSR_Operations_Team add member test_login 
 GO

  

I recommend you try out User-Defined Server Roles and see if it can help you in your day-to-day administration work.