跨資料庫存取而不想在別的資料庫額外設定權限EXECUTE AS的用法

最近接到一個需求,需要跨資料庫存取,而不想在別的資料庫額外設定權限,
所以就開始了解一下EXECUTE AS的用法

[注意]執行以下Demo Code需要AdventureWorks資料庫

-- 建立測試資料庫TEST1
CREATE DATABASE [TEST1]
GO

-- 建立測試Login帳戶Brad
CREATE LOGIN [Brad]
WITH PASSWORD = N'P@ssw0rd',
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF,
DEFAULT_DATABASE = [TEST1],
DEFAULT_LANGUAGE = [繁體中文]
GO

-- 建立TEST1對應的User帳戶brad
USE [TEST1]
GO
CREATE USER [Brad]
GO

-- 建立PROCEDURE With EXECUTE AS SELF讀取別的資料庫的資料
CREATE PROCEDURE [usp_GetAdvWorkEmployee]
WITH EXECUTE AS SELF
AS
SELECT SUSER_NAME(), USER_NAME(), COUNT(*)
FROM [AdventureWorks].[HumanResources].[Employee]
GO

-- 給予EXECUTE權限給Brad
GRANT EXECUTE ON [usp_GetAdvWorkEmployee] to [Brad]
GO

-- 從AdventureWork資料庫產生一些資料到TEST1資料庫
SELECT * INTO dbo.Employee FROM AdventureWorks.HumanResources.Employee;
GO

-- 建立一個Procedure JOIN外部資料庫的資料表
CREATE PROCEDURE [usp_EmployeeDetail]
WITH EXECUTE AS SELF
AS
SELECT a.EmployeeID, a.Title, b.AddressID ,b.ModifiedDate
FROM dbo.Employee a LEFT JOIN AdventureWorks.HumanResources.EmployeeAddress b
ON a.EmployeeID=b.EmployeeID
GO

-- 給予EXECUTE權限給Brad
GRANT EXECUTE ON [usp_EmployeeDetail] to [Brad]
GO

-- Create function with EXECUTE AS SELF
USE [TEST1]
GO
CREATE FUNCTION ufn_GetAdvWorkEmployee()
RETURNS @retTable TABLE
(
EmployeeID int,
Title nvarchar(50)
)
WITH EXECUTE AS SELF
AS
BEGIN
INSERT INTO @retTable SELECT EmployeeID,Title FROM [AdventureWorks].[HumanResources].[Employee]
RETURN
END;
GO

-- 給予SELECT權限給Brad
GRANT SELECT ON ufn_GetAdvWorkEmployee to [Brad]
GO

-- 此時使用brad登入到SQL Server執行以下2個Procedure仍會出現錯誤訊息
USE [TEST1]
GO
EXECUTE usp_GetAdvWorkEmployee;
GO
EXECUTE usp_EmployeeDetail;
GO
-- Msg 916, Level 14, State 1, Procedure usp_GetAdvWorkEmployee, Line 4
-- 伺服器主體 "sa" 在目前的安全性內容下無法存取資料庫 "AdventureWorks"。

-- 將測試資料庫的TRUSTWORTHY改為ON
USE MASTER
GO
ALTER DATABASE [TEST1] SET TRUSTWORTHY ON
GO

-- 此時使用brad登入到SQL Server執行以下2個Procedure就可以執行成功
USE [TEST1]
GO
EXECUTE usp_GetAdvWorkEmployee;
GO
EXECUTE usp_EmployeeDetail;
GO

-- 刪除資料庫
USE MASTER
GO
DROP DATABASE [TEST1]
GO
-- 刪除Login登入帳戶
DROP LOGIN [Brad]
GO