Using EXECUTE AS in Modules
EXECUTE AS can be used to define the execution context of the following user-defined modules: functions, procedures, queues, and triggers. For example, the execution context can be switched from the caller of the module to the owner of the module, or to a specified user. In earlier versions of SQL Server, these modules always execute in the context of the caller of the module.
By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on any objects that are 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. Users of the module only need permissions to execute the module itself; explicit permissions on the referenced objects are not required. Only the user that the module is running as must have permissions on the objects that are accessed by the module.
EXECUTE AS CALLER
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
Mary creates a stored procedure that references a table that she does not own but has SELECT permissions on. She specifies EXECUTE AS CALLER in the CREATE PROCEDURE statement, as shown in this example:
CREATE PROCEDURE AccessTable
WITH EXECUTE AS CALLER
AS SELECT * FROM dbo.SomeTable;
Mary then grants EXECUTE permissions on the stored procedure to Scott. When Scott executes the stored procedure, the Database Engine verifies that he (the caller) has permission to execute the stored procedure. Scott has EXECUTE permission, but because Mary is not the owner of the referenced table, the Database Engine checks to see whether Scott has permissions on the table. If Scott does not have permissions, the stored procedure statement fails.
EXECUTE AS user_name
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
Mary creates a stored procedure that references a table that she does not own but has SELECT permissions on. She specifies EXECUTE AS 'Mary' in the CREATE PROCEDURE statement, as shown in this example:
CREATE PROCEDURE AccessMyTable
WITH EXECUTE AS 'Mary'
AS SELECT * FROM dbo.MyTable;
Mary grants EXECUTE permissions on the stored procedure to Scott. When Scott executes the stored procedure, the Database Engine verifies that he has permission to execute the stored procedure; however, permissions for the referenced table are checked for Mary. In this scenario, even though Scott does not directly have SELECT permissions on the table, he can access the data through the procedure, because Mary, in whose context the procedure is running, has permissions to access the data in the table.
EXECUTE AS SELF
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
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
Mary creates a stored procedure that references a table that she owns. She specifies EXECUTE AS OWNER in the CREATE PROCEDURE statement, as shown in this example:
CREATE PROCEDURE Mary.AccessMyTable
WITH EXECUTE AS OWNER
AS SELECT * FROM Mary.MyTable;
Mary grants EXECUTE permissions on the stored procedure to Scott. When Scott executes the stored procedure, the Database Engine verifies that he has permission to execute the stored procedure; however, permissions for the referenced table are checked for Mary because she is the current owner of the procedure. Mary decides to leave the company and changes ownership of the procedure and table to Tom. When Scott executes the stored procedure after the ownership change, he is still able to access the data through the procedure because OWNER is automatically mapped to Tom.