EXECUTE AS Clause (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.
By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on 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. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module.
Transact-SQL syntax conventions
Syntax
This section describes SQL Server syntax for EXECUTE AS
.
Functions (except inline table-valued functions), stored procedures, and DML triggers:
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }
DDL triggers with database scope:
{ EXEC | EXECUTE } AS { CALLER | SELF | 'user_name' }
DDL triggers with server scope and logon triggers:
{ EXEC | EXECUTE } AS { CALLER | SELF | 'login_name' }
Queues:
{ EXEC | EXECUTE } AS { SELF | OWNER | 'user_name' }
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
CALLER
Specifies the statements inside the module are executed in the context of the caller of the module. The user executing the module must have appropriate permissions not only on the module itself, but also on any database objects that are referenced by the module.
CALLER
is the default for all modules except queues, and is the same as SQL Server 2005 (9.x) behavior.
CALLER
can't be specified in a CREATE QUEUE
or ALTER QUEUE
statement.
SELF
EXECUTE AS SELF
is equivalent to EXECUTE AS <user_name>
, where the specified user is the person creating or altering the module. The actual user ID of the person creating or modifying the modules is stored in the execute_as_principal_id
column in the sys.sql_modules
or sys.service_queues
catalog view.
SELF
is the default for queues.
Note
To change the user ID of the execute_as_principal_id
column in the sys.service_queues
catalog view, you must explicitly specify the EXECUTE AS
setting in the ALTER QUEUE
statement.
OWNER
Specifies that the statements inside the module execute in the context of the current owner of the module. If the module doesn't have a specified owner, the owner of the schema of the module is used. OWNER
can't be specified for DDL or logon triggers.
Important
OWNER
must map to a singleton account, and can't be a role or group.
'user_name'
Specifies the statements inside the module execute in the context of the user specified in user_name. Permissions for any objects within the module are verified against user_name. user_name can't be specified for DDL triggers with server scope or logon triggers. Use login_name instead.
user_name must exist in the current database and must be a singleton account. user_name can't be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService
, NT AUTHORITY\NetworkService
, or NT AUTHORITY\LocalSystem
.
The user ID of the execution context is stored in metadata and can be viewed in the execute_as_principal_id
column in the sys.sql_modules
or sys.assembly_modules
catalog view.
'login_name'
Specifies the statements inside the module execute in the context of the SQL Server login specified in login_name. Permissions for any objects within the module are verified against login_name. login_name can be specified only for DDL triggers with server scope or logon triggers.
login_name can't be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService
, NT AUTHORITY\NetworkService
, or NT AUTHORITY\LocalSystem
.
Remarks
How the Database Engine evaluates permissions on the objects that are referenced in the module depends on the ownership chain that exists between calling objects and referenced objects. In earlier versions of SQL Server, ownership chaining was the only method available to avoid having to grant the calling user access to all referenced objects.
Ownership chaining has the following limitations:
- Applies only to DML statements:
SELECT
,INSERT
,UPDATE
, andDELETE
. - The owners of the calling and the called objects must be the same.
- Doesn't apply to dynamic queries inside the module.
Regardless of the execution context that is specified in the module, the following actions always apply:
When the module is executed, the Database Engine first verifies that the user executing the module has
EXECUTE
permission on the module.Ownership chaining rules continue to apply. This means if the owners of the calling and called objects are the same, no permissions are checked on the underlying objects.
When a user executes a module that has been specified to run in a context other than CALLER
, the user's permission to execute the module is checked, but additional permissions checks on objects that are accessed by the module are performed against the user account specified in the EXECUTE AS
clause. The user executing the module is, in effect, impersonating the specified user.
The context specified in the EXECUTE AS
clause of the module is valid only for the duration of the module execution. Context reverts to the caller when the module execution is completed.
Specify a user or login name
A database user or server login specified in the EXECUTE AS
clause of a module can't be dropped until the module is modified to execute under another context.
The user or login name specified in EXECUTE AS
clause must exist as a principal in sys.database_principals
or sys.server_principals
, respectively, or else the create or alter module operation fails. Additionally, the user that creates or alters the module must have IMPERSONATE permissions on the principal.
If the user has implicit access to the database or instance of SQL Server through a Windows group membership, the user specified in the EXECUTE AS
clause is implicitly created when the module is created when one of the following requirements exists:
- The specified user or login is a member of the sysadmin fixed server role.
- The user that is creating the module has permission to create principals.
When neither of these requirements are met, the create module operation fails.
Important
If the SQL Server (MSSQLSERVER) service is running as a local account (local service or local user account), it will not have privileges to obtain the group memberships of a Windows domain account that is specified in the EXECUTE AS
clause. This will cause the execution of the module to fail.
For example, assume the following conditions:
CompanyDomain\SQLUsers
group has access to theSales
database.CompanyDomain\SqlUser1
is a member ofSQLUsers
and therefore has access to theSales
database.The user that is creating or altering the module has permissions to create principals.
When the following CREATE PROCEDURE
statement is run, the CompanyDomain\SqlUser1
is implicitly created as a database principal in the Sales
database.
USE Sales;
GO
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'CompanyDomain\SqlUser1'
AS
SELECT USER_NAME();
GO
Use EXECUTE AS CALLER stand-alone statement
Use the EXECUTE AS CALLER
stand-alone statement inside a module to set the execution context to the caller of the module.
Assume the following stored procedure is called by SqlUser2
.
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'SqlUser1'
AS
SELECT USER_NAME(); -- Shows execution context is set to SqlUser1.
EXECUTE AS CALLER;
SELECT USER_NAME(); -- Shows execution context is set to SqlUser2, the caller of the module.
REVERT;
SELECT USER_NAME(); -- Shows execution context is set to SqlUser1.
GO
Use EXECUTE AS to define custom permission sets
Specifying an execution context for a module can be useful when you want to define custom permission sets. For example, some actions, such as TRUNCATE TABLE
don't have grantable permissions. By incorporating the TRUNCATE TABLE
statement within a module and specifying that module execute as a user who has permissions to alter the table, you can extend the permissions to truncate the table to the user to whom you grant EXECUTE
permissions on the module.
To view the definition of the module with the specified execution context, use the sys.sql_modules (Transact-SQL) catalog view.
Best practice
Specify a login or user that has the least privileges required to perform the operations defined in the module. For example, don't specify a database owner account unless those permissions are required.
Permissions
To execute a module specified with EXECUTE AS
, the caller must have EXECUTE
permissions on the module.
To execute a CLR module specified with EXECUTE
AS that accesses resources in another database or server, the target database or server must trust the authenticator of the database from which the module originates (the source database).
To specify the EXECUTE AS
clause when you create or modify a module, you must have IMPERSONATE
permissions on the specified principal and also permissions to create the module. You can always impersonate yourself. When no execution context is specified or EXECUTE AS CALLER
is specified, IMPERSONATE
permissions aren't required.
To specify a login_name or user_name that has implicit access to the database through a Windows group membership, you must have CONTROL
permissions on the database.
Examples
The following example creates a stored procedure in the AdventureWorks2022 database and assigns the execution context to OWNER
.
CREATE PROCEDURE HumanResources.uspEmployeesInDepartment @DeptValue INT
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON;
SELECT e.BusinessEntityID,
c.LastName,
c.FirstName,
e.JobTitle
FROM Person.Person AS c
INNER JOIN HumanResources.Employee AS e
ON c.BusinessEntityID = e.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE edh.DepartmentID = @DeptValue
ORDER BY c.LastName,
c.FirstName;
GO
-- Execute the stored procedure by specifying department 5.
EXECUTE HumanResources.uspEmployeesInDepartment 5;
GO