[SQL] 資料庫還原到另一台主機無法登入 - 第二回

摘要:[SQL] 資料庫還原到另一台主機無法登入 - 第二回

 

當將資料庫由A移至B時,會導致原本屬於資料庫的使用者無法正常在B登入到資料庫」,之前是使用「sp_change_users_login」方法來恢復使用者的登入權限(詳細的解說請看前一篇),某日在Terry兄 的部落格也看到使用這個方法來解決,但小朱兄建議用「Alter User」來解決,所以今天剛好有個機會需要還原資料庫,也順便來試試怎麼使用這個方式來解決。

◎◎為何要改用新方法呢?MSDN 給了一段解釋
將現有的資料庫使用者對應至 SQL Server 登入。未來的 Microsoft SQL Server 版本將移除這項功能。請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。 請改用 ALTER USER

底下來說明一下怎麼進行

  1. 先到 B還原你的資料庫(B也就是你要還原的目的主機)
  2. 觀察還原資料庫裡的帳號
  3. 建立新的使用者「Create user」
  4. 切換資料庫
  5. 修改使用者「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