SETUSER Statement

Quick Links:

Go to Parameters

Go to Usage

Go to Standards

Go to Permissions

Syntax

SETUSER userID

Parameters

(back to top)

• UserID – must be the name of an existing user or role that has a login password.

Usage

(back to top)

At-least criteria validation occurs when the SETUSER statement is executed, not when the SET USER system privilege is granted.

To terminate a successful impersonation, issue the SETUSER statement without specifying a userID.

Standards

(back to top)

ANSI SQL–Compliance level: Transact-SQL extension.

Permissions

(back to top)

Requires the following:

• The impersonator has been granted the right to impersonate the target user.
• The impersonator has, at minimum, all the roles and system privileges granted to the target user.
• The impersonator has been granted the said roles and system privileges with similar or higher administrative rights.
  Note: For the purposes of meeting administrative rights criteria, the WITH ADMIN OPTION and WITH ADMIN ONLY OPTION clauses are considered to grant similar administrative rights. They are also considered to grant higher administrative rights than the WITH NO ADMIN OPTION clause. For example, User1 is granted Role1 with the WITH ADMIN OPTION clause, User2 is granted Role1 with the WITH ADMIN ONLY clause, and User3 is granted Role1 with the WITH NO ADMIN OPTION clause. User1 and User2 are said to be granted Role1 with similar administrative rights. User1 and User2 are also said to be granted Role1 with higher administrative rights than User3.
• If the target user has been granted a system privilege that supports extensions, the clauses used to grant the system privilege to the impersonator are a super-set of those used for the target user. Only the SET USER and CHANGE PASSWORD system privileges support extensions.
  • The ANY clause is considered a super-set of the target_roles_list and target_users_list clauses. If the target user has been granted the SET USER system privilege with an ANY grant, the impersonator must also have the ANY grant.
  • If the target user has been granted the SET USER system privilege with both the target_roles_list and target_users_list clauses, the impersonator must also have been granted the system privilege with the two clauses, and the target list of each clause must be equal to, or a super set of, the corresponding clause grant of the target user. For example, if the target lists of both the impersonator and target user contain User1, User2 and Role1, Role2, respectively, the target list grants for each clause are said to be equal. Alternately, if the target list grants of the impersonator contain User1, User2, and Role1, Role2, respectively, while the target list grants of the target user contain User1 and Role2 only, the target list grants of the impersonator are said to be a super-set of the target user.
  • If the target user has been granted the SET USER system privilege with a single target list clause, the target list of the impersonator must be equal to or a super-set of the list of the target user. For example, the target_user_list of both the impersonator and the target user contain User1 and User2 (equal) or the impersonator list contains User1, User2, while the target user contains User2; User1, User2 (impersonator list) is a super-set of User2 (target user list).
  • By definition, a user can always impersonate himself or herself. Therefore, if the target user is granted the right to impersonate the impersonator, this does not violate the equal to or a super-set of criteria requirement of the impersonator. For example, User3 is the impersonator and User4 is the target user. The target_user_list for User3 contains User4 and User5. The target_user_list for User4 contains User3 and User5. If you remove the impersonator from the target list, the target list of User3 meets the criteria requirement.