create role

Description

Creates a user-defined role; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified role at creation. You can also associate a password with the role at the time that the role is created.

Syntax

create role role_name [with passwd "password" 
	[, {passwd expiration | min passwd length | 
	max failed_logins} option_value]]

Parameters

role_name

is the name of the new role, which must be unique to the server and conform to the rules for identifiers. role_name cannot be a variable.

with passwd

attaches a password the user must enter to activate the role.

password

is the password to attach to the role. Passwords must be at least 6 characters in length and must conform to the rules for identifiers. You cannot use variables for passwords.

passwd expiration

password expiration interval specifies the password expiration interval in days. It can be any value between 0 and 32767, inclusive. For example, if you create a new login on August 1, 2007 at 10:30 a.m., with a password expiration interval of 30 days, the password expires on August 31, 2007 at 10:30 a.m.

min passwd length

specifies the minimum password length required for the specified role.

max failed_logins

specifies the number of allowable failed login attempts for the specified login.

option_value

specifies the value for passwd expiration, min passwd length, or max failed_logins.

Examples

Example 1

Creates a role named doctor_role:

create role doctor_role

Example 2

Creates a role named doctor_role with the password “physician”:

create role doctor_role with passwd "physician"

Example 3

Sets passwd expiration to 7 days. The password for the role expires at the time of day that the password was last changed after the specified period has passed (in this example, 7 days):

create role intern_role with passwd "temp244", 
passwd expiration 7

Example 4

Sets the maximum number of failed logins allowed for intern_role:

create role intern_role with passwd "temp244" 
max failed_logins 20

Example 5

Sets the minimum password length for intern_role:

create role intern_role with passwd "temp244", 
min passwd length 0

Usage


Restrictions

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for create role differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, you must be a user with the manage roles privilege.

Granular permissions disabled

With granular permissions disabled, you must be a user with sso_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

85

roles

create role, drop role, alter role, grant role, or revoke role

  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

See also

Commands alter role, drop role, grant, revoke, set

System procedures sp_activeroles, sp_displaylogin, sp_displayroles, sp_helprotect, sp_modifylogin