Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The User Defined Server Roles in SQL Server 2012

By Daniel Calbimonte,

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.

References

Total article views: 3494 | Views in the last 30 days: 8
 
Related Articles
BLOG

Creating and Managing User-Defined Roles in SQL Server 2012

SQL Server 2012 introduces user-defined server roles. These user-defined server roles are similar to...

BLOG

Querying Microsoft SQL Server : Defining Variables

Querying Microsoft SQL Server : Defining Variables: Defining Variables in SQL Server: Like other...

BLOG

Defining Variables

Querying Microsoft SQL Server : Defining Variables: Defining Variables in SQL Server: Like other...

FORUM

Transferring User Defined Datatype to new server !!!!!!

Transferring User Defined Datatype to new server !!!!!!

FORUM

view defination

view defination

Tags
roles    
security    
server roles    
sql server 2012    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones