SQL Server 2012 - User-Defined Server Roles

SQL Server provides server-level roles to help you manage the permissions on a server. These roles are security principals that group other principals. Server-level roles are server-wide in their permissions scope. (Roles are like groups in the Windows operating system.)
Fixed server roles are provided for convenience and backward compatibility. Assign more specific permissions whenever possible.
SQL Server provides nine fixed server roles. The permissions that are granted to the fixed server roles cannot be changed. Beginning with SQL Server 2012, you can create user-defined server roles and add server-level permissions to the user-defined server roles.
You can add server-level principals (SQL Server logins, Windows accounts, and Windows groups) into server-level roles. Each member of a fixed server role can add other logins to that same role. Members of user-defined server roles cannot add other server principals to the role.

Syntax

CREATE SERVER ROLE role_name [ AUTHORIZATION server_principal

Permissions

---

Requires CREATE SERVER ROLE permission or membership in the sysadmin fixed server role.

Also requires IMPERSONATE on the server_principal for logins, ALTER permission for server roles used as the server_principal, or membership in a Windows group that is used as the server_principal.

This will fire the Audit Server Principal Management event with the object type set to server role and event type to add.

When you use the AUTHORIZATION option to assign server role ownership, the following permissions are also required:

• To assign ownership of a server role to another login, requires IMPERSONATE permission on that login.
• To assign ownership of a server role to another server role, requires membership in the recipient server role or ALTER permission on that server role.

Examples

---

A. Creating a server role that is owned by a login

The following example creates the server role buyers that is owned by login BenMiller.

USE master; CREATE SERVER ROLE buyers AUTHORIZATION BenMiller; GO

B. Creating a server role that is owned by a fixed server role

The following example creates the server role auditors that is owned the securityadmin fixed server role.

USE master; CREATE SERVER ROLE auditors AUTHORIZATION securityadmin; GO

To drop a server role

The following example drops the server role purchasing.







 



DROP SERVER ROLE purchasing;
GO

D. To view role membership

To view role membership, use the Server Role (Members) page in SQL Server Management Studio or execute the following query:



 



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
ORDER BY  SP.name,  SP2.name

E. To view role membership

To determine whether a server role owns another server role, execute the following query:







SELECT SP1.name AS RoleOwner, SP2.name AS Server_Role
FROM sys.server_principals AS SP1
JOIN sys.server_principals AS SP2
    ON SP1.principal_id = SP2.owning_principal_id 
ORDER BY SP1.name ;