Skip to content

Latest commit

 

History

History
101 lines (73 loc) · 4.86 KB

create-server-role-transact-sql.md

File metadata and controls

101 lines (73 loc) · 4.86 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
CREATE SERVER ROLE (Transact-SQL)
CREATE SERVER ROLE (Transact-SQL)
VanMSFT
vanto
07/13/2023
sql
t-sql
reference
SERVER_ROLE_TSQL
CREATE SERVER ROLE
SERVER ROLE
CREATE_SERVER_ROLE_TSQL
SERVER ROLE
SERVER ROLE, CREATE
CREATE SERVER ROLE statement
ROLE
user-defined server roles [SQL Server]
roles, server
TSQL

CREATE SERVER ROLE (Transact-SQL)

[!INCLUDE SQL Server]

Creates a new user-defined server role.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

CREATE SERVER ROLE role_name [ AUTHORIZATION server_principal ]

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

role_name

The name of the server role to be created.

AUTHORIZATION server_principal

The login that will own the new server role. If no login is specified, the server role will be owned by the login that executes CREATE SERVER ROLE.

Remarks

Server roles are server-level securables. After you create a server role, configure the server-level permissions of the role by using GRANT, DENY, and REVOKE. To add logins to or remove logins from a server role, use ALTER SERVER ROLE (Transact-SQL). To drop a server role, use DROP SERVER ROLE (Transact-SQL). For more information, see sys.server_principals (Transact-SQL).

You can view the server roles by querying the sys.server_role_members and sys.server_principals catalog views.

Server roles cannot be granted permission on database-level securables. To create database roles, see CREATE ROLE (Transact-SQL).

For information about designing a permissions system, see Getting Started with Database Engine Permissions.

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 fires 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. Create 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. Create 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

Next steps