REVOKE CHANGE PASSWORD Statement

Syntax

REVOKE [ ADMIN OPTION FOR ] CHANGE PASSWORD
   [(target_user_list 
      | ANY 
      | ANY WITH ROLES target_role_list  )]
   FROM userID [,...]

Parameters

target_user_list – users the grantee has the potential to impersonate. The list must consist of existing users or user-extended roles with login passwords. Separate the userIDs in the list with commas.

ANY – all database users with login passwords become potential target users to manage passwords for each grantee.

ANY WITH ROLEStarget_role_list – list of target roles for each grantee. Any users who are granted any of the target roles become potential target users for each grantee. The target_role_list must consist of existing roles and the users who are granted said roles must consist of database users with login passwords. Use commas to separate multiple userIDs.

userID – must be the name of an existing user or role that has a login password. Separate multiple userIDs with commas.

Examples

Example 1 – removes the ability of Joe to manage the passwords of Sallyor Bob:
```
REVOKE CHANGE PASSWORD (Sally, Bob) FROM Joe
```
Example 2 – if the CHANGE PASSWORD system privilege was originally granted to Sam with the WITH ADMIN OPTION clause, this example removes the ability of Sam to grant the CHANGE PASSWORD system privilege to another user, but still allows Sam to manage passwords for those users specified in the original GRANT CHANGE PASSWORD statement. However, if the CHANGE PASSWORD system privilege was originally granted to Sam with the WITH ADMIN ONLY OPTION clause, this example removes all permissions to the system privilege from Sam.
```
REVOKE ADMIN OPTION FOR CHANGE PASSWORD FROM Sam
```

Usage

Depending on how the CHANGE PASSWORD system privilege was initially granted, using the ADMIN OPTION FOR clause when revoking the CHANGE PASSWORD system privilege has different results. If the CHANGE PASSWORD system privilege was originally granted with the WITH ADMIN OPTION clause, including the ADMIN OPTION FOR clause in the revoke statement revokes only the ability to administer the CHANGE PASSWORD system privilege (that is, grant the system privilege to another user). The ability to actually manage passwords for other users remains. However, if the CHANGE PASSWORD system privilege was originally granted with the WITH ADMIN ONLY OPTION clause, including the ADMIN OPTION FOR clause in the revoke statement is semantically equivalent to revoking the entire CHANGE PASSWORD system privilege. Finally, if the CHANGE PASSWORD system privilege was originally granted with the WITH NO ADMIN OPTION clause, and the ADMIN OPTION FOR clause is included in the revoke statement, nothing is revoked because there were no administrative rights granted in the first place.

You can revoke the CHANGE PASSWORD system privilege from any combination of users and roles granted.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Requires the CHANGE PASSWORD system privilege granted with administrative rights.