201010281610MS-SQL 資料庫還原到另一台主機無法登入

這是個備份還原的問題!當做資料救援、系統移機、資料轉匯到另一台主機時都會發現資料庫轉移(Backup → Copy → Restore)明明都正常,系統端的 SQL connection 也都變更了,怎麼會無法連線呢?

這時會出現下面的錯誤訊息畫面



這時你就會去察看資料庫,你會發現該帳號存在於[資料庫]→[DB_Name]→[Security]→[Users]

但~不存在於 [資料庫]→[Security]→[Users],也因為不存在於此所以無法登入,自然無法存取資料庫了!

------------------------------------------------------

這是因為使用者的身份、權限在 整體DB某一DB 裡的意義是不同的

↓ 在某一DB裡,設定的是這個使用者所擁有的身份別 (dbowner、datareader......) 與某物件(Table、View......)的權限。

某物件(Table、View......)的權限(Select、insert、alter......)

↓ 而在整體DB裡,設定的是能否登入?帳號密碼?資料庫管理角色?

↓ 資料庫管理角色

↓ 具有哪些資料庫的使用權限

↓ 是否可以登入

如果上述的說明還是覺得虛虛的,不妨看下圖的例子!

當你將 SQL1 的 DB1 做 Full Backup →複製到目的主機→然後 Restore 在 SQL2,這時他會

 

  • 將屬於SQL1 的 DB1 的資訊完整打包
  • 還原時完整重現在 SQL2 的 DB1
Q:那為何無法登入?
原因就在於:系統的角色、能否登入、使用者的帳號密碼、使用者是對應哪一個資料庫---都記錄在 整體DB 的設定裡。

 


Q:這是SQL的 Bug 嗎? 不是~

Q:那為何還原時不順便還原 整體DB 的相關帳號資訊呢?

A:試想~如果、萬一 SQL2 的 DB8 已經、又剛好有使用 sman 或 Superman 這個帳號,那一還原 Restore 豈不是大亂了

所以資料庫還原時,並不會幫你還原 整體DB 使用者帳號資訊部份

甚至你手動在 SQL2 裡增加 sman 帳號,並且 apply 到 DB1 時,系統就會發出錯誤訊息,說該帳號已經存在(是阿~看上面的解說圖 3rd sman是已經存在了)

勤勞一點的人,會把 SQL2 的 DB1 的 sman 帳號刪除,然後重建帳號,然後重新設定各個物件應該有的屬性。

萬一系統有2000個物件,該帳號用了500的物件,又個別有不同的權限,不瘋掉才奇怪。

*-*-*-*-*-*-*

不用這麼的勤勞,你只需執行一行預存程式(Store Procedure)就搞定了!

sp_change_users_login

 

sp_change_users_login [ @Action = ] 'action' 
    [ , [ @UserNamePattern = ] 'user' ] 
    [ , [ @LoginName = ] 'login' ] 
    [ , [ @Password = ] 'password' ]
[;]
[ @Action = ] 'action'  描述此程序所要執行的動作
Auto_Fix:將目前資料庫中 sys.database_principals 系統目錄檢視的使用者項目連結到相同名稱的 SQL Server 登入。

[ @UserNamePattern = ] 'user這是目前資料庫中的使用者名稱
[ @LoginName = ] 'login這是 SQL Server 登入的名稱
[ @Password = ] 'password這是指派給藉由指定 Auto_Fix 所建立之新 SQL Server 登入的密碼

執行的例子:

EXEC sp_change_users_login 'Auto_Fix', '帳號', NULL, '密碼';

EXEC sp_change_users_login 'Auto_Fix', 'sman', NULL, 's2d3f46g63';

重點在於執行前必須先將執行目標選到你的資料庫名稱



執行完後會出現類似下面的訊息!

The row for user 'sman' will be fixed by updating its login link to a login already in existence. The number of orphaned users fixed by updating users was 1. The number of orphaned users fixed by adding new logins and then updating users was 0.

---中文版的文字---
除非有衝突,否則會將使用者 'sman' 的資料列之登入更新為新的登入,來修復該資料列。 藉由更新使用者而修復的被遺棄使用者的數量為 0。 藉由加入新的登入再更新使用者而修復的被遺棄使用者的數量為 1。

~End

 

回應
Google Search
Google
累積 | 今日
loading......
平均分數:0 顆星
投票人數:0
我要評分:
Google