One of the advancement which is introduced in SQL Server 2012 is user-defined server roles. These user-defined server roles are quite similar to the existing fixed server roles but the only difference with the previous fixed server roles is that they can only be created by SQL Server Administrator user. SQL Administrators can assign, create, delete or manage the user-defined server roles using SQL query or SQL Server Management Studio (GUI). These User-defined roles can further be added as member of an existing user-defined or fixed server role. We can assign only server-wide permissions to the user-defined server roles like SQL Server logins.
In order to find the number of permissions that can be granted use the below query:
Select * from sys.fn_builtin_permissions(DEFAULT) Where [class_desc] IN (‘ENDPOINT’, ‘LOGIN’, ‘SERVER’, ‘AVAILABILITY GROUP’, ‘SERVER ROLE’) ORDER BY [class_desc], [permission_name]Assign Roles with T-SQL
One can create a user defined server role(s) in number of ways e.g. T-SQL or the SQL Server Management Studio UI.
We can create a user defined server role using T-SQL with the help of 3 different commands:
1) Create Server Role statement will create the user defined server’s role;
2) Alter Server Role will add a user to the server’s role;
3) Grant query will grant the required right(s) to the created role.
The above 3 commands are shown in the below code snapshot, which shows creation of a user defined server’s role called “ViewableServerRole”. Then it shows that one user is added to the mentioned role called “NewLogin” and the View Server State right is given or granted to the role. For granting additional users this same right, we simply need to add new users to this fixed server’s role using the Alter Server’s Role line:
USE [master] GO CREATE SERVER ROLE [ViewableServerRole] AUTHORIZATION [sa] GO ALTER SERVER ROLE [ViewableServerRole] ADD MEMBER [NewLogin] GO GRANT VIEW SERVER STATE TO [ViewableServerRole] GO
One can also remove an already assigned user from a user defined server’s role with the Alter Server Role command. In place of Add Member statement, use Drop Member for this purpose:
ALTER SERVER ROLE [ViewableServerRole] DROP MEMBER [NewLogin] GO
Assign Roles with SQL Server Management Studio One can easily do the same actions (as above) with the help of SQL Server Management Studio UI.
Steps: The steps included in assigning roles with SQL Server Management Studio are below mentioned.
1. To create a user defined server role, connect to the database.
2. Go to connection’s Security tab and then Server Roles within it.
3. Right click on Server Roles folder and then select New Server Role option from the context menu. (Refer below image).
4. Once the “New Server Role” window gets open, mention the Server’s role name and the Owner (optional), then choose from the Securables option and in the end Permissions, which you need the role member(s) to have, with them. (Refer below image)
5. Once you have completed the fields on the General tab, select Members tab just below the general tab and add SQL Server Login(s) that will become the member(s) of the mentioned user defined server role. (Refer below image)
6. Once you are done with the selection part of the Role Members, select Memberships tab which is after Member tab. With this tab, one can mention the server roles for which the mentioned user defined server role is a member. If you select server role (ViewableServerRole) on current screen, then the users (Role Members) within this user defined server role will also have same rights granted by that server role.
If you were intended to create user defined role(s) and want to make it a member of the “serveradmin” fixed server role (for example) (Refer below image), then all member(s) of the user defined server role will not effectively become members of the “serveradmin” fixed server role by default. For nesting domain group(s)/database role(s), one need to exercise great care while nesting roles so that one doesn’t grant users rights as well.
User defined server role has large number of uses like, one can grant instance level right(s) to simplify managing the rights or one can create a subordinate Administrator role that can grant few rights, but not the full system’s administrator rights.