REVOKE SET USER Statement

Syntax

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

Parameters

target_user_list – must consist of existing users with login passwords and is the potential list of target users who can no longer be impersonated by grantee users. Separate the user IDs in the list with commas.
ANY – If specified, the potential list of target users for each grantee consists of all database users with login passwords.
ANY WITH ROLES target_role_list – If specified, the target_role_list must consist of existing roles, and the potential list of target users for each grantee must consist of database users with login passwords that have a subset of roles in target_role_list. Separate the list of roles with commas.
userID – Each userID must be the name of an existing user or immutable role. The list must consist of existing users with login passwords. Separate the userIDs in the list with commas.

Examples

Example 1 – stops Bob from being able to impersonate Sally or Bob.
```
REVOKE SET USER (Sally, Bob) FROM Bob
```
Example 2 – if the SET USER system privilege was originally granted to Sam with the WITH ADMIN OPTION clause, this example removes the ability of Sam to grant the SET USER system privilege to another user, but still allows Sam to impersonate those users already granted to him or her. However, if the SET USER 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 SET USER FROM Sam
```

Usage

Depending on how the SET USER system privilege was initially granted, using the ADMIN OPTION FOR clause when revoking the SET USER system privilege has different results. If you the SET USER 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 SET USER system privilege (that is, grant the system privilege to another user). The ability to actually impersonate another user remains. However, if the SET USER 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 SET USER system privilege. Finally, if the SET USER system privilege was originally grant 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 system privileges granted in the first place.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Requires the SET USER system privilege granted with administrative rights.