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.

USE Master

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:

USE master

GO

SELECT * FROM sys.fn_builtin_permissions(DEFAULT)

WHERE class_desc IN (‘ENDPOINT’,'LOGIN’,'SERVER’,'AVAILABILITY GROUP’,'SERVER ROLE’)

ORDER BY permission_name

GO

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’

Go

SELECT SUSER_SNAME()

Go

Create  database MyServerroleDB

Go

Create login abc with password=’abc123′ , check_policy= off

Go

Revert

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’

Go

SHUTDOWN

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

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:

SSMS%

License: Image author owned

Tags:

ABOUT THE AUTHOR:

Freelance consultant and content training author. Please visit mcsesqlserver.com for more information about my work!

Leave A Response

CommentLuv badge