alter role

Syntax

alter role role1 {add | drop} exclusive 
	{membership | activation} role2 

alter role role_name [add passwd "password" | 
	drop passwd] [lock | unlock]

alter role {role_name | "all overrides"} 
	set {passwd expiration | min passwd length | 
	max failed_logins} option_value 

Parameters

• role1 – is one role in a mutually exclusive relationship.
• add – adds a role in a mutually exclusive relationship; adds a password to a role.
• drop – drops a role in a mutually exclusive relationship; drops a password from a role.
• exclusive – makes both named roles mutually exclusive.
• membership – does not allow you to grant users both roles at the same time.
• activation – allows you to grant a user both roles at the same time, but does not allow the user to activate both roles at the same time.
• role2 – is the other role in a mutually exclusive relationship.
• role_name – is the name of the role for which you want to add, drop, or change a password. Use role_name to specify the password expiration interval, the minimum password length, and the maximum number of failed logins.
• passwd – adds or drops a password to a role.
• password – is the password to add to a role. You cannot use variables for passwords. For rules on passwords, see Managing Adaptive Server Logins, Database Users, and Client Connections in the System Administration Guide, Volume 1.
• lock – locks the specified role.
• unlock – unlocks the specified role.
• all overrides – applies the setting that follows to the entire server rather than to a specific role.
• set – activates the option that follows it.
• passwd expiration – specifies the password expiration interval in days. It can be any value between 0 – 32767, inclusive.
• min passwd length – specifies the minimum length allowed for the specified password.
• max failed_logins – specifies the maximum number of failed login attempts allowed for the specified password.
• option_value – specifies the value for passwd expiration, min passwd length, or max failed_logins. To set all overrides, set the value of option_value to -1.

Examples

• Example 1 – Defines intern_role and specialist_role as mutually exclusive at the membership level:

  alter role intern_role add exclusive membership 
      specialist_role

• Example 2 – Defines roles as mutually exclusive at the membership level and at the activation level:

  alter role specialist_role add exclusive membership 
      intern_role
  alter role intern_role add exclusive activation 
      surgeon_role

• Example 3 – Adds a password to an existing role:

  alter role doctor_role add passwd "physician"

• Example 4 – Drops a password from an existing role:

  alter role doctor_role drop passwd

• Example 5 – Locks physician_role:

  alter role physician_role lock

• Example 6 – Unlocks physician_role:

  alter role physician_role unlock

• Example 7 – Changes the maximum number of failed logins allowed for physician_role to 5:

  alter role physician_role set max failed_logins 5

• Example 8 – Sets the minimum password length for physician_role, an existing role, to five characters:

  alter role physician_role set min passwd length 5

• Example 9 – Overrides the minimum password length of all roles:

  alter role "all overrides" set min passwd length -1

• Example 10 – Removes the overrides for the maximum failed logins for all roles:

  alter role "all overrides" set max failed_logins -1

Usage

• The alter role command defines mutually exclusive relationships between roles, and adds, drops, and changes passwords for roles.

• The all overrides parameter removes the system overrides that were set using sp_configure with any of the following parameters:

  • passwd expiration

  • max failed_logins

  • min passwd length

  Dropping the role password removes the overrides for the password expiration and the maximum failed logins options.

  Password complexity checks are set at the login level using create login or alter login. Set the options at the global level using sp_passwordpolicy or sp_configure. See Manage Roles in the Security Administration Guide.

• When you use alter role to lock or unlock roles, you set (or unset) the locksuid, lockdate, and lockreason columns that are added to syssrvroles.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Auditing

Values in event and extrainfo columns of sysaudits are: