Introduction
In versions before SQL Server 2012, we only had fixed server roles in SQL Server. This was a security hole in our systems. SQL Server 2012 introduces user defined server roles and this article describes how to work with them.
Getting Started
To get started, open SQL Server Management Studio and go to the Security Node. Expand the Security Node.
Right click on the Server roles and select New Server Role. This option is one of the new SQL Server 2012 features.
Specify the name, the owner of the role and the securables.
Specify the explicit permissions required on databases, credentials, connections or other permissions.
Press the add button to add the members of the new server role and add some members.
You can also add this role to other roles.
Creating the custom server role using the T-SQL
If you want to create a custom server role you can use the following sentences:
USE [master] GO CREATE SERVER ROLE [customServerRole] AUTHORIZATION [sa]
CustomServerRole is the new server role created and sa is the owner.
The following example adds the SQL logins jane to the Server role named customeServerRole:
ALTER SERVER ROLE [customServerRole] ADD MEMBER [jane]
Note that the sp_addsrvrolemember stored procedure cannot be used with the new used defined server role. According to the books online, some stored procedures will be removed in future versions.
ALTER SERVER ROLE [customServerRole] DROP MEMBER [jane]
Note that the sp_dropsrvrolemember cannot be used with the new used defined server role. According to the documentation, some stored procedures will be removed in future versions.
The following example adds the customeServerRole to the setupadmin role:
ALTER SERVER ROLE [setupadmin] ADD MEMBER [customServerRole]
The following example Grants bulk operation, alter connection permissions to the Server role:
GRANT ADMINISTER BULK OPERATIONS TO [customServerRole] GRANT ALTER ANY CONNECTION TO [customServerRole]
The sp_helpsrvrole only works with fixed server roles. To list all the Server Roles, use the following query:
SELECT * FROM sys.server_principals WHERE type = 'R'
If you run the sp_helpsrvrole using a user defined server role, you will receive an error message:
EXEC sp_helpsrvrolemember 'customServerRole'
Msg 15412, Level 11, State 1, Procedure sp_helpsrvrolemember, Line 10
'customServerRole' is not a known fixed role.
That’s why we need to use queries to the system views. The following example lists only the user defined server roles use this query:
SELECT * FROM sys.server_principals WHERE type = 'R' and is_fixed_role =0 and name<>'public';
The following example shows how to list the members of a user defined server role named customServerRole:
SELECT SRM.role_principal_id, SP.name AS Role_Name, SRM.member_principal_id, SP2.name AS Member_Name FROM sys.server_role_members AS SRM JOIN sys.server_principals AS SP ON SRM.Role_principal_id = SP.principal_id JOIN sys.server_principals AS SP2 ON SRM.member_principal_id = SP2.principal_id WHERE sp.name='customServerRole' -- ORDER BY SP.name, SP2.name
The following example verifies if the jane is member of the custom server role named customServerRole:
select IS_SRVROLEMEMBER ( 'customServerRole','janet')
The following example shows how to remove the user defined server role:
DROP SERVER ROLE [customServerRole]
Conclusion
The new user defined server role is a secure and new feature added in SQL Server 2012. Some stored procedures used in fixed server role are not valid anymore and will be removed and replaced to support this new feature in the future.
