The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

SQL 資料庫還原到到另一台後無法登入要怎麼解決

在資料庫轉移時,從原主機「卸載(Detach)」資料庫��案,並移至新主機「附加(Attach)」上去,在「新主機」沒有預先設定 Login 的情況下,資料庫中的 User 將無法刪除,也無法新增同名的 Login,相信有些人有遇到這樣的困擾過!

會出現這個問題並不是 SQL Server 有 Bug,而是因為你每次建立的登入帳號都會在 SQL Server 中對應一個 SID,資料庫的「使用者」與「登入」的對應是用 SID 來比對的,所以你還原資料庫到另一台機器後,該資料庫中的使用者的 sid 無法比對到該主機 SQL Server 登入帳號中的 sid 所致,所以即便你自己新增了一個「登入」帳號,因為 SID 不一樣,所以也是沒有該資料庫的權限。

你可以用以下這段 SQL 查詢到你 SQL Server 中每個登入帳號的 SID:

[code:tsql]

select sid, name, dbname, language, loginname from dbo.syslogins;

[/code]

如果你要查詢某一個資料庫中所有使用者的 sid 可以用以下 SQL 語法:

[code:tsql]

SELECT * FROM [資料庫名稱].sys.sysusers;

[/code]

解決的方法很簡單,江湖一點訣而已啦。

你可以使用 sp_change_users_login 這個預儲程序,將目前資料庫中的資料庫使用者與 SQL Server 登入連結,用法如下:

[code:tsql]

EXEC sp_change_users_login 'Auto_Fix', '帳號', NULL, '一個複雜一點的密碼';

[/code]

範例說明一下,請先開啟 Management Studio,開啟一個 SQL Query 視窗,假設你的資料庫叫做 AdventureWorks 而使用者是 John,底下這段 SQL 給他執行下去,你的 SQL Server 就會幫你自動新增叫做 John 的「登入」了:

[code:tsql]

USE AdventureWorks;

GO

EXEC sp_change_users_login 'Auto_Fix', 'John', NULL, 'F830dkr10_d34$55';

[/code]

備註1:sp_change_users_login 無法搭配 Windows 登入一起使用。

備註2:執行這行指令一定要用 sa 帳戶或系統管理者的權限才能正確執行。

完整的參考請見【SQL Server 2005 線上叢書】的說明:http://msdn2.microsoft.com/zh-tw/library/ms174378.aspx

 

以下 T-SQL 可幫助你列出所有遺失登入的資料庫使用者:

[code:tsql]

sp_change_users_login @Action='Report'

[/code]

也可以在手動建立登入帳戶時直接指定 SID,這樣在建立完登入後就會自動與資料庫使用者連結在一起:

[code:tsql]

CREATE LOGIN [TESTUser] WITH PASSWORD=N'1234',

DEFAULT_DATABASE=[AdvantureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF,

SID = 0x98C57C7B2F64C040886FC5F6F1579C62

[/code]