User defined database roles had existed in SQL Server for a very long time. But never did it facilitate a user defined server role for an instance. Say for example I would want one of my junior colleague to have a set of permissions which would be a combination of a few of the rights of a server admin role and a dbcreator role. Prior to SQL Server 2012 that was not possible. One way was that I make him a member of sysadmin role which is not at all a feasible solution. But with SQL Server 2012 you can now create user defined server roles and assign only server /scope permissions needed for the new role.
Setting up the User Defined Server role
As we all know that there are 9 fixed server roles which cannot be modified. This way there is no flexibility by which you could separate the DBA’s in groups which was an issue when you had a group of DBA’s working on the same production system. So you had to make them a member of any of those nine fixed roles. User defined roles has overcome these shortcomings to a certain level.
The user defined server roles can be created using TSQL or SSMS. The basic steps would be:
- Creating the Server Login
- Creating the Server Role
- Make the login member of the role
- Assign the requisite permissions to that role
One good feature of server level role is that a particular user defined server role can belong to another user defined server role in a hierarchical manner. This adds more flexibility to this feature.
As I said earlier you can create the user defined server roles using TSQL and SSMS. First let’s have a look at the TSQL way.
First you define the server role.
Create Server Role MyServerRole
Then create a login.
Create login Sachin with password=’sachin123′ ,check_policy=off
Now let’s grant a few permissions to the newly created server role. But before that we can list out the permissions that can be assigned to a user defined server role. Running the following query would list out those permissions:
SELECT * FROM sys.fn_builtin_permissions(DEFAULT)
WHERE class_desc IN (‘ENDPOINT’,'LOGIN’,'SERVER’,'AVAILABILITY GROUP’,'SERVER ROLE’)
ORDER BY permission_name
Of the list displayed we would grant user the rights for creating database, shutting down the server and alter any login which are the permissions for the db_creator, serveadmin and securityadmin fixed server roles respectively.
GRANT CREATE ANY DATABASE TO [MyServerRole]
GRANT SHUTDOWN TO [MyServerRole]
GRANT ALTER ANY LOGIN TO [MyServerRole]
Now that the permissions have been assigned we would now make the login sachin as the member of this user defined server role
ALTER SERVER ROLE [MyServerRole] ADD MEMBER Sachin
The login Sachin is now a member of the server role MyServerRole we can now test the permissions assigned to the login.
EXECUTE AS LOGIN=’Sachin’
Create database MyServerroleDB
Create login abc with password=’abc123′ , check_policy= off
If you execute the above statement you will see that the login can create a database (which is a db_creator server role permission) and create a login (which is a securityadmin server role permission).You can test the permission for shutting down the server using the following command:
EXECUTE AS LOGIN=’Sachin’
A member of a fixed server role have rights to add a new members to the same role by default but this is not the case with the user defined server role.You would have to explicitly grant Alter rights to the member.
GRANT ALTER ON SERVER ROLE::[ MyServerRole] TO [Sachin] GO
This way you can have a much more granular control over the server role in which only a few members of the role or no one can add members to the role. But if you would like to override this and want that a member can add new members to the same role, you could do it by using the following command:
GRANT ALTER ON SERVER ROLE::[ MyServerRole] TO [MyServerRole] GO
Creating Server Roles using SSMS
Creating a user defined role using SSMS is quite easy. Just right click the server roles node under the Security Node in SSMS
In the New Server Role creation wizard, you first specify the server role name and then select the securable and the associated permissions. You can specify GRANT, WITH GRANT or DENY for each permission for the selected securables the way it’s done for user defined database roles or users:
License: Image author owned