EXECUTE AS 子句 (Transact-SQL)EXECUTE AS Clause (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL Server 中,您可以定義下列使用者自訂模組的執行內容:函數 (內嵌資料表值函式除外)、程序、佇列和觸發程序。In SQL ServerSQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.

您可以指定執行模組的內容,來控制 Database EngineDatabase Engine 是使用哪一個使用者帳戶,以驗證該模組參考之物件的權限。By specifying the context in which the module is executed, you can control which user account the Database EngineDatabase 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 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- SQL Server Syntax  
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' }   
  
-- Azure SQL Database Syntax  
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' }  
  

引數Arguments

CALLERCALLER
指定模組內的陳述式,是在該模組的呼叫者內容當中執行。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 是所有模組 (佇列除外) 的預設值,而且與 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 行為一樣。CALLER is the default for all modules except queues, and is the same as SQL Server 2005 (9.x)SQL Server 2005 (9.x) behavior.

CALLER 不能在 CREATE QUEUE 或 ALTER QUEUE 陳述式中指定。CALLER cannot be specified in a CREATE QUEUE or ALTER QUEUE statement.

SELFSELF
EXECUTE AS SELF 相當於 EXECUTE AS user_name,其中指定的使用者就是建立或變更模組的人員。EXECUTE AS SELF is equivalent to EXECUTE AS user_name, where the specified user is the person creating or altering the module. 建立或修改模組之人員的實際使用者識別碼,是儲存在 sys.sql_modulessys.service_queues 目錄檢視的 execute_as_principal_id 資料行中。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 是佇列的預設值。SELF is the default for queues.

注意

若要變更 sys.service_queues 目錄檢視中 execute_as_principal_id 的使用者識別碼,您必須在 ALTER QUEUE 陳述式中明確指定 EXECUTE AS 設定。To change the user ID of the execute_as_principal_id in the sys.service_queues catalog view, you must explicitly specify the EXECUTE AS setting in the ALTER QUEUE statement.

OWNEROWNER
指定模組內的陳述式,是在該模組目前擁有者的內容中執行。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. DDL 或登入觸發程序不能指定 OWNER。OWNER cannot be specified for DDL or logon triggers.

重要

OWNER 必須對應到單一帳戶,而且不可以是角色或群組。OWNER must map to a singleton account and cannot be a role or group.

' user_name '' user_name '
指定模組內的陳述式,在 user_name 指定的使用者內容中執行。Specifies the statements inside the module execute in the context of the user specified in user_name. 會針對 user_name 來驗證模組內任何物件的權限。Permissions for any objects within the module are verified against user_name. user_name 無法針對具有伺服器範圍的 DDL 觸發程序或登入觸發程序來指定。user_name cannot be specified for DDL triggers with server scope or logon triggers. 請改為使用 login_nameUse login_name instead.

user_name 必須存在於目前資料庫中,而且必須是單一帳戶。user_name must exist in the current database and must be a singleton account. user_name 不可以是群組、角色、憑證、金鑰或內建帳戶,例如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。user_name cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

執行內容的使用者識別碼儲存在中繼資料中,可以在 sys.sql_modulessys.assembly_modules 目錄檢視的 execute_as_principal_id 資料行中檢視。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 '' login_name '
指定模組內的陳述式,在 login_name 所指定的 SQL ServerSQL Server 登入內容中執行。Specifies the statements inside the module execute in the context of the SQL ServerSQL Server login specified in login_name. 會針對 login_name 來驗證模組內任何物件的權限。Permissions for any objects within the module are verified against login_name. login_name 只能針對具有伺服器範圍的 DDL 觸發程序或登入觸發程序來指定。login_name can be specified only for DDL triggers with server scope or logon triggers.

login_name 不可以是群組、角色、憑證、金鑰或內建帳戶,例如 NT AUTHORITY\LocalService、NT AUTHORITY\NetworkService 或 NT AUTHORITY\LocalSystem。login_name cannot be a group, role, certificate, key, or built-in account, such as NT AUTHORITY\LocalService, NT AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

RemarksRemarks

Database EngineDatabase Engine 如何評估模組內參考物件的權限,會隨著呼叫物件和所參考物件之間的擁有權鏈結而不同。How the Database EngineDatabase 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. 在舊版的 SQL ServerSQL Server 中,擁有權鏈結是唯一可以避免授與參考物件存取權給呼叫使用者的方法。In earlier versions of SQL ServerSQL 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:

  • 只適用於 DML 陳述式:SELECT、INSERT、UPDATE 和 DELETE。Applies only to DML statements: SELECT, INSERT, UPDATE, and DELETE.

  • 呼叫和被呼叫物件的擁有者必須相同。The owners of the calling and the called objects must be the same.

  • 不適用於模組內的動態查詢。Does not apply to dynamic queries inside the module.

無論模組指定的執行內容為何,下列動作一定適用:Regardless of the execution context that is specified in the module, the following actions always apply:

  • 在執行模組時,Database EngineDatabase Engine 會先驗證執行模組的使用者,對該模組具有 EXECUTE 權限。When the module is executed, the Database EngineDatabase 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.

當使用者執行被指定要在 CALLER 以外之內容執行的模組時,會檢查該使用者執行模組的權限,但是該模組存取物件的其他權限,則是針對 EXECUTE AS 子句中指定的使用者帳戶來執行。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.

該模組 EXECUTE AS 子句中所指定的內容,只在執行該模組時有效。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.

指定使用者或登入名稱Specifying a User or Login Name

模組 EXECUTE AS 子句所指定的資料庫使用者或伺服器登入,必須等到修改模組,在其他內容下執行之後,才能夠卸除。A database user or server login specified in the EXECUTE AS clause of a module cannot be dropped until the module has been modified to execute under another context.

EXECUTE AS 子句所指定的使用者或登入名稱,必須以主體身分分別存在於 sys.database_principalssys.server_principals 中,否則建立或變更模組作業就會失敗。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. 另外,建立或變更模組的使用者,必須具有該主體的 IMPERSONATE 權限。Additionally, the user that creates or alters the module must have IMPERSONATE permissions on the principal.

如果使用者透過 Windows 群組成員資格,對資料庫或 SQL ServerSQL Server 執行個體具有隱含的存取權,則在 EXECUTE AS 子句中指定的使用者,會在下列一項需求成立,並且在建立好模組時,以隱含方式建立:If the user has implicit access to the database or instance of SQL ServerSQL 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 exist:

  • 指定的使用者或登入,是 sysadmin 固定伺服器角色的成員。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.

重要

如果 SQL ServerSQL Server (MSSQLSERVER) 服務是作為本機帳戶 (本機服務或本機使用者帳戶) 使用,就沒有權限可以取得 EXECUTE AS 子句指定之 Windows 網域帳戶的群組成員資格。If the SQL ServerSQL 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 群組有權存取 Sales 資料庫。CompanyDomain\SQLUsers group has access to the Sales database.

  • CompanyDomain\SqlUser1SQLUsers 的成員,因此有權存取 Sales 資料庫。CompanyDomain\SqlUser1 is a member of SQLUsers and, therefore, has access to the Sales database.

  • 建立或變更該模組的使用者,有權建立主體。The user that is creating or altering the module has permissions to create principals.

在執行下列 CREATE PROCEDURE 陳述式時,CompanyDomain\SqlUser1 會隱含建立為 Sales 資料庫中的資料庫主體。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  

使用 EXECUTE AS CALLER 獨立陳述式Using EXECUTE AS CALLER Stand-Alone Statement

請在模組內使用 EXECUTE AS CALLER 獨立陳述式,將執行內容設為模組的呼叫者。Use the EXECUTE AS CALLER stand-alone statement inside a module to set the execution context to the caller of the module.

假設下列預存程序是由 SqlUser2 呼叫。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  

使用 EXECUTE AS 來定義自訂權限集合Using EXECUTE AS to Define Custom Permission Sets

當您要定義自訂權限集合時,不妨指定模組的執行內容。Specifying an execution context for a module can be very useful when you want to define custom permission sets. 例如,有些動作 (例如 TRUNCATE TABLE) 沒有可以授與的權限。For example, some actions, such as TRUNCATE TABLE, do not have grantable permissions. 您可以將 TRUNCATE TABLE 陳述式併入模組中,並且指定該模組以有權變更資料表的使用者身分來擴充權限,將資料表在您授與該模組 EXECUTE 權限的使用者處截斷。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.

若要以指定的執行內容來檢視模組定義,請使用 sys.sql_modules (Transact-SQL) 目錄檢視。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, do not specify a database owner account unless those permissions are required.

權限Permissions

若要執行指定了 EXECUTE AS 的模組,呼叫者必須具有該模組的 EXECUTE 權限。To execute a module specified with EXECUTE AS, the caller must have EXECUTE permissions on the module.

若要執行指定了 EXECUTE AS 的 CLR 模組,以存取另一個資料庫或伺服器中的資源,則目標資料庫或伺服器必須信任該模組發源之資料庫 (來源資料庫) 的驗證器。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).

若要在建立或修改模組時指定 EXECUTE AS 子句,您必須具有指定之主體的 IMPERSONATE 權限,同時也必須有權建立該模組。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. 如果未指定任何執行內容,或者沒有指定 EXECUTE AS CALLER,就不需要 IMPERSONATE 權限。When no execution context is specified or EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.

若要指定透過 Windows 群組成員資格以具有資料庫之隱含存取權的 login_nameuser_name,您必須具有該資料庫的 CONTROL 權限。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

以下範例會在 AdventureWorks2012AdventureWorks2012 資料庫中建立一個預存程序,並將執行內容指派給 OWNERThe following example creates a stored procedure in the AdventureWorks2012AdventureWorks2012 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  
  

另請參閱See Also

sys.assembly_modules (Transact-SQL) sys.assembly_modules (Transact-SQL)
sys.sql_modules (Transact-SQL) sys.sql_modules (Transact-SQL)
sys.service_queues (Transact-SQL) sys.service_queues (Transact-SQL)
REVERT (Transact-SQL) REVERT (Transact-SQL)
EXECUTE AS (Transact-SQL)EXECUTE AS (Transact-SQL)