Applied SQL…

In SQL Server 2012 you can now create an user-defined server role and configure server level permissions for it. In previous versions this was not possible. If we had to delegate someone with administrative tasks we had no choice but to assign more rights and access than required. With SQL Server 2012, user-defined server roles can be created and configured with specific permissions for specific set of DBA’s.

Let us understand with an example how we can create an user defined server role.
Step 1: Right click on Server roles and select ‘New Server Role‘
Step 2-> As the dialog box opens, type in a server role name -> set the owner to a preferred login. In our case we would choose sa.
Step 3 -> Choose an option\s from Securables window. In our case we chose Servers. Under servers you will find the name of the server. Select the option. Below in the permissions window select the following as shown in the snapshot.
Step 4 -> Click on OK. You will find the new server role under the server roles in SSMS
Step 5 -> Now let us add a login to this new role. Right click on the ServerRole1 -> Click on Properties -> On the members tab click on Add
Step 6 -> Add a login that you want a to give membership to this role. Click on OK.
So now you have successfully given a particular login few administrative rights that is required rather than granting it a privilege like sysadmin.However, one limitation of the user-defined server roles is that they cannot be granted permission on database level securables. Below is the script for the entire action we did.

USE [master]
GO
CREATE SERVER ROLE [ServerRole1]
AUTHORIZATION [sa]
GO
use [master]
GO
GRANT ALTER SERVER STATE TO [ServerRole1]
GO
use [master]
GO
GRANT ALTER TRACE TO [ServerRole1]
GO
use [master]
GO
GRANT CONNECT SQL TO [ServerRole1]
GO
ALTER SERVER ROLE [ServerRole1]
ADD MEMBER [testdb]
GO