The execution context of a session or module can be explicitly changed by specifying a user or login name in an EXECUTE AS statement. The impersonation remains in effect until one of the following events occurs:

• The session is dropped.
  
• Context is switched to another login or user.
  
• Context is reverted to the previous execution context.
  

Using EXECUTE AS to explicitly impersonate another user is similar to SETUSER in earlier versions of SQL Server. For more information, see EXECUTE AS vs. SETUSER.

Explicit Server-Level Context Switching

-- Switch execution context to the adventure-works\dan1 login account.
EXECUTE AS LOGIN = 'adventure-works\dan1';
-- Create the new login account.
CREATE LOGIN Jinghao1 WITH PASSWORD = '3KHJ6dhx(0xVYsdf';
-- Revert to the previous execution context.
REVERT;

Explicit Database-Level Context Switching

-- EXECUTE AS USER = 'dan1';
-- Create a table in dan1's default schema
CREATE TABLE t_NewTable( data nvarchar(100) );
go
-- Revert to the previous execution context.
REVERT
go;

The execution context of a module, such as a stored procedure, trigger, queue, or user-defined function, can be implicitly changed by specifying a user or login name in an EXECUTE AS clause in the module definition.

By specifying the context in which the module is executed, you can control which user account SQL Server uses to validate permissions on any objects referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions can be granted to users on the module itself, without having to grant explicit permissions on the referenced objects. Only the user that the module is impersonating needs to have permissions on the objects accessed by the module.

The level of impersonation is determined by the type of module in which the impersonation is defined.

Server-level impersonation can be defined in the following:

• DDL triggers
  

The scope of server-level impersonation is the same as that previously defined in "Explicit Server-Level Context Switching."

Database-level impersonation can be defined in the following:

• DML triggers
  
• Queues
  
• Stored procedures
  
• User-defined functions
  
• The scope of database-level impersonation is the same as that previously defined in "Explicit Database-Level Context Switching."
  
• For more information about implicit context switching, see Using EXECUTE AS in Modules.
  

CREATE PROCEDURE dbo.usp_TruncateMyTable
WITH EXECUTE AS SELF
AS TRUNCATE TABLE MyDB..MyTable;

Other Resources

Help and Information