GRANT SET USER Statement

A user can be granted the ability to impersonate any user in the database (ANY) or only specific users (target_users_list) or members of specific roles (ANY WITH ROLES target_roles_list). Administrative rights to the SET USER system privilege can only be granted when using the ANY clause.

Syntax

GRANT SET USER ( target_usesr_list | ANY | ANY WITH ROLES target_roles_list )
  TO userID [,...] 
  [ WITH ADMIN [ONLY] OPTION | WITH NO ADMIN OPTION]

Parameters

target_users_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 for impersonation for each grantee.
ANY WITH ROLES target_roles_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. There are two restrictions when using this method.
- Only those users who have been granted a subset of the target_role_list can be impersonated by a grantee.
- Any user being impersonated must have exactly the exact subset of target_role_list; no additional roles are allowed.
userID – must be the name of an existing user or role that has a login password. Separate multiple userIDs with commas.
WITH ADMIN OPTION – (valid in conjunction with the ANY clause only) The user can both issue the SETUSER command to impersonate another user and grant the SET USER system privilege to another user.
WITH ADMIN ONLY OPTION – (valid in conjunction with the ANY clause only) The user can grant the SET USER system privilege to another user, but cannot issue the SETUSER command to impersonate another user.
WITH NO ADMIN OPTION – the user can issue the SETUSER command to impersonate another user, but cannot grant the SET USER system privilege to another user.

Examples

Example 1 – grants Sally and Laurel the ability to impersonate Bob, Sam, and Peter.
```
GRANT SET USER (Bob, Sam, Peter) TO (Sally, Laurel)
```
Example 2 – grants Mary the right to grant the SET USER system privilege to any user in the database. However, since the system privilege is granted with the WITH ADMIN ONLY OPTION clause, Mary cannot impersonate any other user.
```
GRANT SET USER (ANY) TO Mary WITH ADMIN ONLY OPTION
```
Example 3 – grants Steve and Joe the ability to impersonate any member of Role1 or Role2.
```
GRANT SET USER (ANY WITH ROLES Role1, Role2) TO Steve, Joe
```

Usage

If no clause is specified, ANY is used by default.

If no administrative clause is specified in the grant statement, the WITH NO ADMIN OPTION clause is used.

If regranting the SET USER system privilege to a user, the effect of the regrant is cumulative.

By default, the SET USER system privilege is granted to the SYS_AUTH_SSO_ROLE compatibility role with the WITH NO ADMIN OPTION clause, if they exist.

The granting of the SET USER system privilege to a user only grants the potential to impersonate another user. Validation of the at-least criteria required to successfully impersonate another user does no occur until the SETUSER statement is issued.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Requires the SET USER system privilege granted with administrative rights.
Each target user specified (target_users_list) is an existing user or user-extended role with a login password.
Each target role specified (target_roles_list) must be an existing user-extended or user-defined role.