Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

SETUSER (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Allows a member of the sysadmin fixed server role or the owner of a database to impersonate another user.

Important note Important

SETUSER is included for backward compatibility only. SETUSER may not be supported in a future release of SQL Server. We recommend that you use EXECUTE AS instead.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

SETUSER [ 'username' [ WITH NORESET ] ] 

' username '

Is the name of a SQL Server or Windows user in the current database that is impersonated. When username is not specified, the original identity of the system administrator or database owner impersonating the user is reset.

WITH NORESET

Specifies that subsequent SETUSER statements (with no specified username) should not reset the user identity to system administrator or database owner.

SETUSER can be used by a member of the sysadmin fixed server role or the owner of a database to adopt the identity of another user to test the permissions of the other user. Membership in the db_owner fixed database role is not sufficient.

Only use SETUSER with SQL Server users. SETUSER is not supported with Windows users. When SETUSER has been used to assume the identity of another user, any objects that the impersonating user creates are owned by the user being impersonated. For example, if the database owner assumes the identity of user Margaret and creates a table called orders, the orders table is owned by Margaret, not the system administrator.

SETUSER remains in effect until another SETUSER statement is issued or until the current database is changed with the USE statement.

Note Note

If SETUSER WITH NORESET is used, the database owner or system administrator must log off and then log on again to reestablish his or her own rights.

Requires membership in the sysadmin fixed server role or must be the owner of the database. Membership in the db_owner fixed database role is not sufficient

The following example shows how the database owner can adopt the identity of another user. User mary has created a table called computer_types. By using SETUSER, the database owner impersonates mary to grant user joe access to the computer_types table, and then resets his or her own identity.

SETUSER 'mary';
GO
GRANT SELECT ON computer_types TO joe;
GO
--To revert to the original user
SETUSER;

Community Additions

ADD
Show:
© 2015 Microsoft