SQL Server 跨資料庫查詢三大方法：三部分命名、OPENROWSET、跨資料庫所有權鏈結

方法一：三部分命名 (同一實例內)

最簡單、效能最佳，格式：[資料庫名稱]..[資料表名稱]

SELECT * 
FROM [AAA]..TableA a 
INNER JOIN [BBB]..TableB b ON a.AccountID = b.ClientID;

方法二：OPENROWSET (跨實例/異構資料源)

啟用 Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

查詢語法

SELECT * 
FROM OPENROWSET(
    'SQLNCLI', 
    'Server=遠端IP;UID=sa;PWD=密碼', 
    '資料庫名.dbo.資料表名'
);

查詢後關閉功能 (安全最佳實踐)

EXEC sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

方法三：跨資料庫所有權鏈結

伺服器層級啟用

EXEC sp_configure 'cross db ownership chaining', 1; RECONFIGURE;

資料庫層級啟用

ALTER DATABASE 資料庫名 SET DB_CHAINING ON;

動態 SQL 限制與解決方案

動態 SQL (EXEC/SP_EXECUTESQL) 下跨資料庫所有權鏈結失效，除非同一使用者存在於兩個資料庫。

SQL Server 2005+ 解決方案：憑證簽署存儲過程

-- 1. 建立憑證
CREATE CERTIFICATE CrossDBCert ENCRYPTION BY PASSWORD = 'StrongPwd' 
WITH SUBJECT = 'Cross DB Access';

-- 2. 在目標資料庫建立使用者並授權
USE TargetDB;
CREATE USER CrossDBUser FROM CERTIFICATE CrossDBCert;
GRANT SELECT ON 目標表 TO CrossDBUser;

-- 3. 對存儲過程簽署
ADD SIGNATURE TO dbo.CrossDBProc BY CERTIFICATE CrossDBCert 
WITH PASSWORD = 'StrongPwd';

方法選擇決策表