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
-- assigning newly created role to existing fixed role
alter server role processadmin add member UDSR_Operations_Team
-- granting permissions to User-Defined Server Roles
grant view server state to UDSR_Operations_Team
grant alter trace to UDSR_Operations_Team
-- assigning SQL login to User-Defined Server Roles
alter server role UDSR_Operations_Team add member test_login
I recommend you try out User-Defined Server Roles and see if it can help you in your day-to-day administration work.