摘要:[SQL] 資料庫還原到另一台主機無法登入 - 第二回
「當將資料庫由A移至B時,會導致原本屬於資料庫的使用者無法正常在B登入到資料庫」,之前是使用「sp_change_users_login」方法來恢復使用者的登入權限(詳細的解說請看前一篇),某日在Terry兄 的部落格也看到使用這個方法來解決,但小朱兄建議用「Alter User」來解決,所以今天剛好有個機會需要還原資料庫,也順便來試試怎麼使用這個方式來解決。
◎◎為何要改用新方法呢?MSDN 給了一段解釋
將現有的資料庫使用者對應至 SQL Server 登入。未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。 請改用 ALTER USER。
底下來說明一下怎麼進行
- 先到 B還原你的資料庫(B也就是你要還原的目的主機)
- 觀察還原資料庫裡的帳號
- 建立新的使用者「Create user」
- 切換資料庫
- 修改使用者「Alter User」
---------------------------------------------------------------------------------------
▼還原你的資料庫▲
還原資料庫的選擇
▼觀察還原資料庫裡的帳號▲
上圖中 fifth 是還原資料庫裡的使用者帳號,接著再觀察 SQL Login 的帳號,
你會發現 fifth 帳號並未出現在 SQL Login ,導致這個帳號並無法正常登入(ㄝ~中間空白的地方,是我系統的帳號,所以將其刪除文字,並不是故意把 fifth 藏起來)
=== 底下開始說明處理的方法 =================
▼建立新的使用者▲
1. 輸入使用者名稱
2. 選擇 SQL Server 驗證(當然你也是可以用 AD 做驗證)
3. 輸入2次密碼
4. 選擇預設的資料庫
5. 按下「確定」~搞定
USE [master]
GO
CREATE LOGIN [fifth] WITH PASSWORD=N'fifth_pass', DEFAULT_DATABASE=[fifth],CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
~~ 但這個時候帳號 fifth 還是無法登入的 ~~
因為.......使用者 fifth 並未授與使用那個DB的權限
▼切換資料庫▲
或用 USE [fifth]
▼修改使用者▲
執行底下這行 T-SQL,指定/修改 fifth 資料庫裡的 fifth 使用者,聯繫到 SQL Login 的使用者 fifth。(好繞舌...XD)
ALTER USER fifth WITH Login = fifth
-------
ALTER USER userName WITH <set_item> [ ,...n ] <set_item> ::= NAME = newUserName | DEFAULT_SCHEMA = schemaName | LOGIN = loginName
執行完這一行後,再到 SQL Login 去看使用者 fifth 的屬性,你就會發現 fifth 被授與連到資料庫 fifth 了。
不過,這種方法的預設結構描述 (schema)是跟使用者同名,如果你跟我一樣是從 SQL 2000 轉匯(還原)到 SQL 2008,因為 SQL 2005 之後對 third part name 有不同的定義跟用法,所以你可以在此檢查動作,將 schema 改回 dbo 或你想變更的結構描述。
▼最後一步▲
當然是用SSMS驗證一下,剛剛建立/修改的帳號是不是能夠登入 SQL,並且擁有正確的權限。
~ End