Server Roles with TSQL

Here’s a few bits of TSQL you can use when working with Server-Level Roles in SQL Server 2012.

List the server roles setup on an instance…

EXEC sp_helpsrvrole;

List the members of a role…

EXEC sp_helpsrvrolemember 'sysadmin';

List the permissions assigned to a server-level role…

EXEC sp_srvrolepermission 'sysadmin';

Test is a login is the member of a role…

SELECT IS_SRVROLEMEMBER('sysadmin', 'DOMAIN\Joe_Bloggs');

A DMV is provided to review server-level role assignments…

SELECT *
FROM sys.server_role_members;

Now something a little more useful to the human eye…

SELECT SUSER_NAME(member_principal_id) AS username,
	   SUSER_NAME(role_principal_id) [role]
FROM sys.server_role_members;

We can also create User-Defined Server-Level Roles in SQL Server 2012.

This example will DENY the VIEW ANY DATABASE permission. First create the role…

USE [master];
GO

CREATE SERVER ROLE [deny_view_any_database];
GO

Add any members to the role…

ALTER SERVER ROLE [deny_view_any_database] ADD MEMBER [DOMAIN\Joe_Bloggs];
GO

Deny the permission from the role…

use [master];
GO

DENY VIEW ANY DATABASE TO [deny_view_any_database];
GO