教學：所有權鏈與上下文切換

適用於：SQL Server Azure SQL 受控執行個體

此教學課程利用案例來說明涉及擁有權鏈結與使用者環境切換的 SQL Server 安全性概念。

注意

若要執行本教學課程中的程式碼，您必須已設定混合模式安全性，並已安裝 AdventureWorks2025 資料庫。如需混合模式安全性的詳細資訊，請參閱選擇驗證模式。

情境

在此狀況下，有兩位使用者需要使用其帳戶來存取 AdventureWorks2025 資料庫內儲存的採購訂單資料。需求如下：

第一個帳戶 (TestManagerUser) 必須能夠看見每筆訂單的所有詳細資料。
第二個帳戶 (TestEmployeeUser) 必須能夠依採購單號，查看對於已收到部分出貨之品項的每張採購單中的採購單號、訂單日期、出貨日期、產品 ID，以及已訂購和已收貨的品項。
其餘所有帳戶必須保有各自現行的權限。
為了滿足此案例的需求，範例將拆成四部分示範擁有權鏈結和內容切換的概念：

設定環境。
建立預存程序用於存取訂單資料。
透過預存程序存取資料。
重設環境。

此範例會在每個程式碼區塊中各行附上說明。若要複製整個範例，請參閱本教學課程結尾處的＜完整範例＞一節。

必要條件

若要完成本教學課程，您需要 SQL Server Management Studio、執行 SQL Server 伺服器的存取權，以及 AdventureWorks2025 資料庫。

如需還原 SQL Server Management Studio 中資料庫的指示，請參閱還原資料庫。

1.設定環境

使用 SQL Server Management Studio 搭配下列程式碼開啟 AdventureWorks2025 資料庫，然後使用 CURRENT_USER Transact-SQL 陳述式確認 dbo 使用者是否顯示為執行內容。

USE AdventureWorks2022;  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO

如需 CURRENT_USER 陳述式的詳細資訊，請參閱 CURRENT_USER (Transact-SQL)。

使用下列程式碼，以 dbo 使用者身分在伺服器和 AdventureWorks2025 資料庫中建立兩位使用者。

CREATE LOGIN TestManagerUser   
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';  
GO  
CREATE USER TestManagerUser   
   FOR LOGIN TestManagerUser  
   WITH DEFAULT_SCHEMA = Purchasing;  
GO   
  
CREATE LOGIN TestEmployeeUser  
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';  
GO  
CREATE USER TestEmployeeUser   
   FOR LOGIN TestEmployeeUser;  
GO

關於該 CREATE USER 聲明的更多資訊，請參見 CREATE USER （Transact-SQL）。關於該 CREATE LOGIN 聲明的更多資訊，請參見 CREATE LOGIN （Transact-SQL）。

使用下列陳述式，將 Purchasing 結構描述的擁有權變更為 TestManagerUser 帳戶。這可讓該帳戶對其所包含的物件使用所有資料操控語言 (DML) 陳述式的存取權限（例如 SELECT 和 INSERT 權限）。 TestManagerUser 也被授與建立預存程序的權限。

/* Change owner of the Purchasing Schema to TestManagerUser */  
ALTER AUTHORIZATION   
   ON SCHEMA::Purchasing   
   TO TestManagerUser;  
GO  
  
GRANT CREATE PROCEDURE   
   TO TestManagerUser   
   WITH GRANT OPTION;  
GO

關於該 GRANT 聲明的更多資訊，請參見 GRANT （Transact-SQL）。如需預存程序的詳細資訊，請參閱預存程序 (資料庫引擎)。若要查看所有資料庫引擎權限的海報，請參閱 https://aka.ms/sql-permissions-poster。

2.建立預存程序來存取資料

若要在資料庫中切換上下文，請使用以下 EXECUTE AS 語句。 EXECUTE AS 需要 IMPERSONATE 權限。

在下列程式碼中使用 EXECUTE AS 陳述式，將內容切換為 TestManagerUser，並建立只顯示 TestEmployeeUser 所需資料的預存程序。為了滿足需求，預存程序接受訂單號碼做為變數且並未顯示財務資訊，而 WHERE 子句則將結果限制在已部分送達的貨品。

EXECUTE AS LOGIN = 'TestManagerUser'  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  
  
/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */  
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int  
AS  
BEGIN   
   SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate  
      , b.ProductID, b.OrderQty, b.ReceivedQty  
   FROM Purchasing.PurchaseOrderHeader a  
      INNER JOIN Purchasing.PurchaseOrderDetail b  
         ON a.PurchaseOrderID = b.PurchaseOrderID  
   WHERE b.OrderQty > b.ReceivedQty  
      AND @ProductID = b.ProductID  
   ORDER BY b.ProductID ASC  
END  
GO

目前 TestEmployeeUser 尚無權存取任何資料庫物件。下列程式碼（仍在 TestManagerUser 內容中）會授予該使用者帳戶透過預存程序查詢基礎資料表資訊的能力。

GRANT EXECUTE  
   ON OBJECT::Purchasing.usp_ShowWaitingItems  
   TO TestEmployeeUser;  
GO

儘管並未明確指定結構描述，此預存程序係屬 Purchasing 結構描述的一部分，因為 TestManagerUser 預設即指派到 Purchasing 結構描述。您可以利用系統目錄資訊找出物件，如下列程式碼所示。

SELECT a.name AS 'Schema'  
   , b.name AS 'Object Name'  
   , b.type AS 'Object Type'  
FROM sys.schemas a  
   INNER JOIN sys.objects b  
      ON a.schema_id = b.schema_id   
WHERE b.name = 'usp_ShowWaitingItems';  
GO

範例的這個部分既已完成，程式碼隨即使用 REVERT 陳述式將內容切換回 dbo。

REVERT;  
GO

關於該 REVERT 聲明的更多資訊，請參見 REVERT （Transact-SQL）。

3.透過預存程序存取資料

TestEmployeeUser 除了登入以及指派給 public 資料庫角色的權限外，對 AdventureWorks2025 資料庫物件沒有任何權限。下列程式碼將因 TestEmployeeUser 試圖存取基底資料表而傳回錯誤。

EXECUTE AS LOGIN = 'TestEmployeeUser'  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  
/* This won't work */  
SELECT *  
FROM Purchasing.PurchaseOrderHeader;  
GO  
SELECT *  
FROM Purchasing.PurchaseOrderDetail;  
GO

傳回的錯誤：

Msg 229, Level 14, State 5, Line 6
The SELECT permission was denied on the object 'PurchaseOrderHeader', database 'AdventureWorks2022', schema 'Purchasing'.

由於 TestManagerUser 憑藉其 Purchasing 結構描述擁有權而成為上一節所建立預存程序中參考之物件的擁有者， TestEmployeeUser 可透過預存程序存取基底資料表。下列程式碼仍舊使用 TestEmployeeUser 內容，傳遞訂單號碼 952 做為參數。

EXEC Purchasing.usp_ShowWaitingItems 952  
GO

4.重設環境

下列程式碼使用 REVERT 命令，將目前帳戶的內容切換回 dbo然後重設環境。

REVERT;  
GO  
ALTER AUTHORIZATION   
ON SCHEMA::Purchasing TO dbo;  
GO  
DROP PROCEDURE Purchasing.usp_ShowWaitingItems;  
GO  
DROP USER TestEmployeeUser;  
GO  
DROP USER TestManagerUser;  
GO  
DROP LOGIN TestEmployeeUser;  
GO  
DROP LOGIN TestManagerUser;  
GO

完整範例

本節顯示完整的範例程式碼。

注意

此程式碼不包含兩個預期的錯誤；這兩個錯誤用來說明 TestEmployeeUser 無法從基底資料表中選取資料。

/*   
Script:       UserContextTutorial.sql  
Author:       Microsoft  
Last Updated: Books Online  
Conditions:   Execute as DBO or sysadmin in the AdventureWorks database  
Section 1:    Configure the Environment   
*/  
USE AdventureWorks2022;  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  
/* Create server and database users */  
CREATE LOGIN TestManagerUser   
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khx';  
  
GO  
  
CREATE USER TestManagerUser   
   FOR LOGIN TestManagerUser  
   WITH DEFAULT_SCHEMA = Purchasing;  
GO   
  
CREATE LOGIN TestEmployeeUser  
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';  
GO  
CREATE USER TestEmployeeUser   
   FOR LOGIN TestEmployeeUser;  
GO   
  
/* Change owner of the Purchasing Schema to TestManagerUser */  
ALTER AUTHORIZATION   
   ON SCHEMA::Purchasing   
   TO TestManagerUser;  
GO  
  
GRANT CREATE PROCEDURE   
   TO TestManagerUser   
   WITH GRANT OPTION;  
GO  
  
/*   
Section 2: Switch Context and Create Objects  
*/  
EXECUTE AS LOGIN = 'TestManagerUser';  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  
  
/* Note: The user that calls the EXECUTE AS statement must have IMPERSONATE permissions on the target principal */  
CREATE PROCEDURE usp_ShowWaitingItems @ProductID int  
AS  
BEGIN   
   SELECT a.PurchaseOrderID, a.OrderDate, a.ShipDate  
      , b.ProductID, b.OrderQty, b.ReceivedQty  
   FROM Purchasing.PurchaseOrderHeader AS a  
      INNER JOIN Purchasing.PurchaseOrderDetail AS b  
         ON a.PurchaseOrderID = b.PurchaseOrderID  
   WHERE b.OrderQty > b.ReceivedQty  
      AND @ProductID = b.ProductID  
   ORDER BY b.ProductID ASC  
END;  
GO  
  
/* Give the employee the ability to run the procedure */  
GRANT EXECUTE   
   ON OBJECT::Purchasing.usp_ShowWaitingItems  
   TO TestEmployeeUser;  
GO   
  
/* Notice that the stored procedure is located in the Purchasing   
schema. This also demonstrates system catalogs */  
SELECT a.name AS 'Schema'  
   , b.name AS 'Object Name'  
   , b.type AS 'Object Type'  
FROM sys.schemas AS a  
   INNER JOIN sys.objects AS b  
      ON a.schema_id = b.schema_id   
WHERE b.name = 'usp_ShowWaitingItems';  
GO  
  
/* Go back to being the dbo user */  
REVERT;  
GO  
  
/*  
Section 3: Switch Context and Observe Security   
*/  
EXECUTE AS LOGIN = 'TestEmployeeUser';  
GO  
SELECT CURRENT_USER AS 'Current User Name';  
GO  
EXEC Purchasing.usp_ShowWaitingItems 952;  
GO  
  
/*   
Section 4: Clean Up Example  
*/  
REVERT;  
GO  
ALTER AUTHORIZATION   
ON SCHEMA::Purchasing TO dbo;  
GO  
DROP PROCEDURE Purchasing.usp_ShowWaitingItems;  
GO  
DROP USER TestEmployeeUser;  
GO  
DROP USER TestManagerUser;  
GO  
DROP LOGIN TestEmployeeUser;  
GO  
DROP LOGIN TestManagerUser;  
GO

SQL Server 資料庫引擎和 Azure SQL 資料庫的安全性

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-07-21