Defines mutually exclusive relationships between roles; adds, drops, and changes passwords for roles; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified role.
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
is one role in a mutually exclusive relationship.
adds a role in a mutually exclusive relationship; adds a password to a role.
drops a role in a mutually exclusive relationship; drops a password from a role.
makes both named roles mutually exclusive.
does not allow you to grant users both roles at the same time.
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.
is the other role in a mutually exclusive relationship.
is the name of the role for which you want to add, drop, or change a password.
adds a password to a role.
is the password to add to a role. Passwords must be at least 6 characters in length and must conform to the rules for identifiers. You cannot use variables for passwords.
locks the specified role.
unlocks the specified role.
applies the setting that follows to the entire server rather than to a specific role.
activates the option that follows it.
specifies the password expiration interval in days. It can be any value between 0 and 32767, inclusive.
specifies the minimum length allowed for the specified password.
specifies the maximum number of failed login attempts allowed for the specified password.
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.
Defines intern_role and specialist_role as mutually exclusive:
alter role intern_role add exclusive membership specialist_role
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
Adds a password to an existing role:
alter role doctor_role add passwd "physician"
Drops a password from an existing role:
alter role doctor_role drop passwd
Locks the role physician_role:
alter role physician_role lock
Unlocks the role physician_role:
alter role physician_role unlock
Changes the maximum number of failed logins allowed for physician_role to 5:
alter role physician_role set max failed_logins 5
Sets the minimum password length for physician_role, an existing role, to five characters:
alter role physician_role set min passwd length 5
Overrides the minimum password length of all roles:
alter role "all overrides" set min passwd length -1
Removes the overrides for the maximum failed logins for all roles:
alter role "all overrides" set max failed_logins -1
The alter role command defines mutually exclusive relationships between roles and adds, drops, and changes passwords for roles.
For more information on altering roles, see the System Administration Guide.
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.
You need not specify the roles in a mutually exclusive relationship or role hierarchy in any particular order.
You can use mutual exclusivity with role hierarchy to impose constraints on user-defined roles.
Mutually exclusive membership is a stronger restriction than mutually exclusive activation. If you define two roles as mutually exclusive at membership, they are implicitly mutually exclusive at activation.
If you define two roles as mutually exclusive at membership, defining them as mutually exclusive at activation has no effect on the membership definitions. Mutual exclusivity at activation is added and dropped independently of mutual exclusivity at membership.
You cannot define two roles as having mutually exclusive after granting both roles to users or roles. Revoke either granted role from existing grantees before attempting to define the roles as mutually exclusive on the membership level.
If two roles are defined as mutually exclusive at activation, the System Security Officer can assign both roles to the same user, but the user cannot activate both roles at the same time.
If the System Security Officer defines two roles as mutually exclusive at activation, and users have already activated both roles or, by default, have set both roles to activate at login, Adaptive Server makes the roles mutually exclusive, but issues a warning message naming specific users with conflicting roles. The users’ activated roles do not change.
To change the password for a role, first drop the existing password, then add the new password, as follows:
alter role doctor_role drop passwd
alter role doctor_role add passwd "physician"
Passwords attached to user-defined roles do
not expire.
SQL92 – Compliance level: Transact-SQL extension.
Only a System Security Officer can execute alter role.
Commands create role, drop role, grant, revoke, set
Functions mut_excl_roles, proc_role, role_contain, role_id, role_name
System procedures sp_activeroles, sp_displaylogin, sp_displayroles, sp_modifylogin
