CREATE ROLE (Transact-SQL)

Creates a user-defined database role in the current database. Database roles allow for the definition of sets of permissions that can be granted to users.

Syntax

CREATE ROLE role_name [ AUTHORIZATION owner_name ]
[ WITH OPTIONS [ = ] option [ ,... n ] ]
[ AS PRINCIPAL principal_id ]
;

Parameters

role_name : The name by which the new database role is known. Role names must be unique within the database.

AUTHORIZATION owner_name : Specifies the owner of the database role. If not specified, the current user becomes the owner. The owner must be a principal (user or role) in the current database.

WITH OPTIONS : Specifies options for the role. Current options include:

DEFAULT_SCHEMA = schema_name: Specifies the default schema for the role. If not specified, the default schema is determined by the user who creates the role.

AS PRINCIPAL principal_id : Creates a role as a proxy for another principal. This is an advanced feature for managing permissions through a proxy.

Permissions

Permissions Required

To create a role, you must have the CREATE ROLE permission on the database. Membership in the db_securityadmin fixed database role also allows you to create roles.

Examples

Example 1: Create a basic database role

SQL

CREATE ROLE AppReaders;

This statement creates a new database role named `AppReaders`. The current user becomes the owner of this role.

Example 2: Create a role with a specific owner and default schema

SQL

CREATE ROLE AppWriters AUTHORIZATION dbo
WITH OPTIONS = (DEFAULT_SCHEMA = AppSchema);

This statement creates a role named `AppWriters`, owned by the `dbo` user, and sets `AppSchema` as its default schema.