EXECUTE AS vs. SETUSER

  • Article

In SQL Server 2005, 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.

Comparing Context Switching Functionality

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).
    SETUSER does not allow an execution context stack to be created.

Reverting to a Previous Context

EXECUTE AS

Use the REVERT statement to return to the previous context. The caller of the REVERT statement must be in the same database where the impersonation occurred.

SETUSER

To return to the previous context, use the SETUSER statement without specifying a user name.

See Also

Concepts

Principals

Other Resources

Context Switching
EXECUTE AS (Transact-SQL)
REVERT (Transact-SQL)
SETUSER (Transact-SQL)
sys.database_principals (Transact-SQL)
sys.server_principals (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance