create role

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

Examples

Usage

  • Use create role from the master database.

  • If you attach a password to the role, the user granted this role must specify the password to activate the role.

    For information on adding a password to a role after creation, see the alter role command.

    Note: Passwords created in versions before 12.x that are attached to user-defined roles do not expire.
  • Role names must be unique to the server.

  • Role names cannot be the same as user names. You can create a role with the same name as a user, but when you grant privileges, SAP ASE resolves naming conflicts by making the grant to the user instead of the role.

    For more information on naming conflicts, see the grant role command.

The restrictions for create role are:
  • The maximum number of roles that can be created per server session is 1024. However, 32 roles are reserved for system roles, such as sa_role and sso_role. Also, a special user-defined role, sa_serverprivs_role was created by SAP ASE. Therefore, the maximum number of user-defined roles that can be created per server session is 991.

  • If you create a role with an attached password, a user cannot activate that role by default at login. Do not create a role with an attached password if the user to whom you grant that role needs to activate the role by default at login.

See also sp_activeroles, sp_displaylogin, sp_displayroles, sp_helprotect in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

SettingDescription
Enabled

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

Disabled

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

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

85

Audit option

roles

Command or access audited

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

Information in extrainfo
  • 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

Related reference
alter role
drop role
grant
revoke
set