CREATE ROLE Statement

Syntax

CREATE [ OR REPLACE ] ROLE role_name | FOR USER userID
[ WITH ADMIN [ ONLY ] admin_name [,...] ]

Parameters

role_name – unless you are using the OR REPLACE clause, role_name cannot already exist in the database.
OR REPLACE – role_name must already exist in the database. All current administrators are replaced by those specified in admin_name [,...] clause.
FOR USER – when using the FOR USER clause without the OR REPLACE, userID must be the name of an existing user that currently does not have the ability to act as a role.
admin_name – list of users to be designated administrators of the role.
WITH ADMIN – each admin_name specified is granted administrative privileges over the role in addition to all underlying system privileges.
WITH ADMIN ONLY – each admin_name specified is granted administrative privileges only over the role, not the underlying system privileges.

Note: If you do not specify an ADMIN clause, the default WITH ADMIN ONLY clause is used and the default administrator is the global roles administrator.

Examples

Example 1 – creates the role Sales as a standalone role. Since no role administrator is specified, any user with the global role administrator is the default administrator of this role.
```
 CREATE ROLE Sales
```
Example 2 – extends the role for existing user Jane.
```
 CREATE OR REPLACE ROLE FOR USER Jane
```
Example 3 – creates the role Finance with Mary and Jeff as role administrators with administrative rights only to the role.
```
 CREATE ROLE Finance 
WITH ADMIN ONLY Mary, Jeff
```
Example 4 – if Finance is an existing role with Mary and Jeff with role administrators, this statement replaces Mary and Jeff with Bob and Sarah as role administrators, this time with administrative rights to the role.
```
 CREATE OR REPLACE ROLE Finance 
WITH ADMIN Bob, Sarah
```

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Create a new role – Requires the MANAGE ROLES system privilege.
OR REPLACE clause – Requires the MANAGE ROLES system privilege along with administrative rights over the role being replaced.