Not an IT pro?

We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second.

EXECUTE AS vs. SETUSER

EXECUTE AS vs. SETUSER

In SQL Server, the context in which a string, command, or module is executed can be explicitly set by using the EXECUTE AS statement. EXECUTE AS replaces the SETUSER statement. For more information about context switching, see
Understanding Context Switching.

Compared to the SETUSER statement, EXECUTE AS has the following advantages:

Server or database principals other than sa or dbo can call EXECUTE AS.

The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal.

SETUSER is restricted to members of the sysadmin fixed server role or db_owner fixed database role.

The scope of impersonation is explicitly defined in the statement.

The specified principal is specified as a LOGIN, a server-level impersonation, or as a USER, a database-level impersonation.

The scope of impersonation in the SETUSER statement is implicit. If the statement is called by a member of sysadmin, server-level impersonation is used. If the statement is called by an account that is dbo, database-level impersonation is used.

The impersonation remains in effect until one of the following occurs:

The session is dropped.

Context is switched to another login or user.

Context is reverted to the previous execution context.

With SETUSER, the impersonation remains in effect until one of the following occurs:

Another SETUSER statement is issued.

The current database is changed with the USE statement by an account that is dbo, or is a member of the sysadmin fixed server role.

You can create an execution context stack by calling the EXECUTE AS statement multiple times across multiple principals. When called, the REVERT statement switches the context to the login or user in the next level up in the context stack. For more information, see
EXECUTE AS (Transact-SQL).