形成SQL Server孤兒帳號的原因及解決用法

2012-01-28

19410
0
T-SQL

前一陣子為了做BI的學習, 將BI Server上的資料做一了份備份.並在測試機將BI Server的資料還原.其實在做這個動作時, 沒有考量太多問題;在測試機上沒有預先設定Login,也嘗試在測試機上建立在SERVER一樣的使用者,但出現一些錯誤!找了一些資料後,才知道當每一次建立一個User時SQL Server會建立新的SID(安全性識別碼), 當User登入時,SQL Server資料庫會將登入的USER的ID和資料庫內的SID做比對,若兩者的SID無法一致時,就產生了錯誤!也就是說,登入是透過SID與使用者建立關聯；如果 SID 在移動後發生不一致，SQL Server 會拒絕使用者存取資料庫。
解決的步驟如下:

利用Management Studio開啟一個SQL Query新視窗,
1.先 use [所要查詢的資料庫名稱]

2.利用sys.sysusers查詢某一個資料庫中有哪些使用者的SID, 語法:
SELECT * FROM [資料庫名稱].sys.sysusers;

3.使用 sp_change_users_login 預儲程序(store Por)將目前資料庫中的使用者與登入使用者做連結,語法:
   use [資料庫名稱];
   EXEC sp_change_users_login 'AUTO_FIX', '帳號', NULL, '密碼';
   當sp_change_users_login後面接著使用 Auto_Fix 時，如果登入不存在，則必須指定 user 和 password；否則必須指定 user，但會忽略 password。
   login 必須是 NULL。user 必須是目前資料庫的有效使用者。不能有其他使用者對應至登入。
    結果訊息如下:
    藉由更新使用者而修復的被遺棄使用者的數量為 0。
    藉由加入新的登入再更新使用者而修復的被遺棄使用者的數量為 1。

這裡稍加解釋一下何時會使用到sp_change_users_login的時機,
1.當SQL版本升級後,執行程式如果遇到錯誤訊息是有關msdb的話,有可能是舊資料庫和新資料庫的登入使用者或角色沒有對應到
2.資料庫的備份檔由A機器還原到B機器上的資料庫上,同時在B機器上建立一個新帳號無法對應原有的帳戶,就形成孤兒帳戶(號).
就如同我的例子一樣.
3.SQL Server重新安裝或SQL版本升級後,只還原使用者資料或附加資料庫方式將資料庫附加
而解決孤兒帳號的用法有三:
1.列出目前資料庫的孤兒帳號
use [資料庫名稱];
EXEC sp_change_users_login 'Report';
(EXEC sp_change_users_login @Action='Report';)

2.自動將使用者帳號所對應的login登入到syslogins中
use [資料庫名稱];
EXEC sp_change_users_login 'AUTO_FIX', '帳號';
當sp_change_users_login後面接著使用 Auto_Fix 時，如果登入不存在，則必須指定 user 和 password；否則必須指定 user，但會忽略 password。
login 必須是 NULL。user 必須是目前資料庫的有效使用者。不能有其他使用者對應至登入。

3.將使用者對應到指定到新登入帳號
/*新登入帳號 TestUser*/
CREATE LOGIN [newuser] WITH PASSWORD=N'aaaa';
EXEC sp_change_users_login 'UPDATE_ONE', 'olduser', 'newuser';

回首頁