EXECUTE AS CALLER specifies that the statements inside the module are executed in the context of the caller of the module.

Use EXECUTE AS CALLER for the following scenarios:

• You want the statements in the module to execute as the calling user.
  
• You want to base permission checks for the statements in the module against the calling user, and rely only on ownership chaining to bypass permission checks on underlying objects. Remember that ownership chaining applies only to DML statements. For more information about ownership chaining, see Ownership Chains.
  
• Your application does not require hiding underlying referenced objects from the user, or you only reference objects of the same ownership and can therefore rely on ownership chaining to provide hiding of schema.
  
• You want to preserve SQL Server 2000 behavior.
  

EXECUTE AS CALLER Scenario

CREATE PROCEDURE AccessTable
WITH EXECUTE AS CALLER
AS SELECT * FROM dbo.SomeTable;

EXECUTE AS user_name, specifies the statements inside the module execute in the context of the user specified in user_name.

Use EXECUTE AS user_name in the following scenarios:

• You want the statements in the module to execute in the context of a specified user.
  
• You cannot rely on ownership chaining (for example, the module accesses objects with different ownership) to hide the underlying schema, and you want to avoid granting permissions on those referenced objects.
  
• You want to create a custom permission set. For example, to provide permissions to DDL operations for which specific permissions cannot ordinarily be granted. For more information, see Using EXECUTE AS to Create Custom Permission Sets.
  

  Note:
  A user that is specified as the execution context of a module cannot be dropped until the execution context of that module has been changed.

EXECUTE AS user_name Scenario

CREATE PROCEDURE AccessMyTable
WITH EXECUTE AS 'Mary'
AS SELECT * FROM dbo.MyTable;

EXECUTE AS SELF is equivalent to EXECUTE AS user_name, where the specified user is the person creating or modifying the module.

Use EXECUTE AS SELF in the following scenarios:

• You want a shortcut to specifying yourself as the user under whose context you want to run the statements of the module you are creating or modifying to run as.
  
• You have an application that creates modules for users calling into it, and you want those modules to be created by using those users as the execution context. In this scenario, you do not know at design time what the calling user name is.
  

EXECUTE AS OWNER specifies the statements inside the module executes in the context of the current owner of the module. If the module does not have a specified owner, the owner of the schema of the module is used.

Use EXECUTE AS OWNER in the following scenario:

• You want to be able to change owner of the module without having to modify the module itself. That is, OWNER automatically maps to the current owner of the module at run time.
  

OWNER is the explicit owner of the module or, if there is not an explicit owner, the owner of the schema of the module at the time the module is executed. OWNER must be a singleton account and not a group or role. The ownership of the module cannot be changed to a group or role when the module specifies EXECUTE AS OWNER and has an explicit owner. The ownership of a schema cannot be changed to a role or group when it contains a module that specifies EXECUTE AS OWNER and the modules does not have an explicit owner.

EXECUTE AS OWNER Scenario

CREATE PROCEDURE Mary.AccessMyTable
WITH EXECUTE AS OWNER
AS SELECT * FROM Mary.MyTable;

Concepts

Other Resources

Help and Information