MS SQL 跨資料庫擁有權鏈 cross db ownership chaining

2015-07-30

日前打算整合多台SQL Server到同一部SQL Server來節省資源。我們有一些核心資料表

是很多系統都需要用到，然而在清查欲搬移的這些DB中赫然發現每一個資料庫都存在一

份這一些核心資料表。這表示前人在開發時就是習慣搬一份核心資料表放在自己DB自己

用，然後再用排程固定去同步這一些核心資料表。

但如果整合這些DB後，勢必不可能再這樣做。每個DB都同步自己的那一份實在有一點蠢

。因此我想將這一些核心資料表獨立出到一個DB中。每個DB針對自己需求再到該核心資

料表的DB去撈資料。日後只要核心資料表DB同步就可以了。

然而原來的程式都已經在RUN了，一旦資料表需要改到別的DB去存取，那勢必要改寫所有

程式。因此我想到的方法就是建View或同義字來解。將核心資料表在每個DB都建成View或

同義字，這樣一來程式就都不用改了。

但用這樣的方式屆時在各帳號權限設定上也是很費工。想了一下偷吃步，也就是今天我們要

介紹的跨資料庫擁有權鏈(預設此功能是關閉)。這項功能簡單的說就是我們在DB1建立一個

跨DB存取資料的物件，例如建立一個View去存取DB2的資料表資料，並將該物件(View)Grant

給帳號User可以Select。然而帳號User並無存取DB2的權限，因此當他Select DB1建立的View

時會產生無權限存取DB2的警告訊息。此時我們只要將DB1及DB2的跨資料庫擁有權鏈功能打

開，User就可以順利的透過DB1上的View取得DB2上的資料表資料。

注意 : 此項功能牽扯到安全性問題，這裡只是介紹。屆時我自己還是會乖乖設權限吧。


/**建立兩個測試資料庫**/

Create Database DB_Chaining1;

Create Database DB_Chaining2;

GO



/**建立測試USER**/

CREATE LOGIN [TestUser] WITH PASSWORD=N'123', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

GO



/**將測試USER帳號加入至DB_Chaining1中並給予db_datareader 權限**/

Use DB_Chaining1

GO

Create User [TestUser] For Login [TestUser];

Alter Role db_datareader Add Member [TestUser];

GO



/**將測試USER帳號加入至DB_Chaining2甚麼權限都不給**/

Use DB_Chaining2

GO

Create User [TestUser] For Login [TestUser];

GO



/**在DB_Chaining2建立一資料表tb1並寫入一筆資料**/

Use DB_Chaining2

GO

Create Table tb1(id int,name nvarchar(10));

Insert Into tb1 Values(1,N'ROCK');

GO



/**在DB_Chaining1建立一View vw_tb1去Select DB_Chaining2.dbo.tb1資料表的資料**/

Use DB_Chaining1

GO

Create View vw_tb1 As Select * From DB_Chaining2.dbo.tb1;

GO

以上就是我們的前置作業，接下來我們利用帳號TestUser測試看看，如下圖所示該帳號

Select DB_Chaining1.dob.vw_tb1時出現無權限Select DB_Chaining2.dbo.tb1的訊息。