資料庫附加或還原後無法登入問題

昨天又有同事在做資料庫mdf附加至至SQL Server後,原來的使用者帳號會無法登入的情況,我想應該很多人都碰過這個問題(不管是用還原或附加的方式都一樣),於是就把發生的原因及解決方法整理一下給大家參考.

先簡單描述一下問題發生的原因,SQL Server的登入可分成login account及database user account, login account是代表這台SQL Server可以合法登入的使用者,但是每一個login account可以使用那些DB呢?這就要靠database user account的設定了,通常,我們在建完login account後,會在各資料庫中去建立user account,比如:

--建立SQL login account
CREATE LOGIN user1 WITH PASSWORD = '1qaz!QAZ'
GO

--在TestDB中為user1 login account建立database user account
Use TestDB
GO
CREATE USER user1 FOR LOGIN user1

這樣的意思就是在TestDB中建立一個user1的database user account, 而這個user account則是與user1這個login account發生關聯(這2個名稱實際上可以不同,不過為了方便管理,通常都設相同),而接下來就可以針對user1這個database user account去設定在TestDB中的權限,比如

EXEC sp_addrolemember 'db_datareader', 'user1'

意思就是把user1這個database user account加入db_datareader這個角色.

了解了login account與database user account的關係後,再來看為何資料庫attach或restore後,用user1帳號登入卻無法使用資料庫的問題,答案就是關聯, 先來看看login account存在master DB中的記錄:

SELECT * FROM master.dbo.syslogins where name='user1'

再來看user1在TestDB database中的記錄:

SELECT * FROM [TestDB].sys.sysusers where name='user1'

各位可以發現這2個系統資料表中都有一個SID的欄位,剛才講的關聯就是SID這個欄位,這2個table中的SID欄位應該是要一樣的,如下圖所示:


到這裡各位應該就可以理解為何attach或restore後會同樣的login account會無法使用資料庫的原因了,那是因為login account是我們在新SQL SERVER中建立的,而database user account是存在我們的DB中,當我們還原或附加DB時,這個SID當然不會與我們在新的SQL Server中所建的SID相同,所以當你在新的SQL SERVER用user1這個login account登入,進到TestDB時卻因為查不到這筆SID的記錄就被拒絕存取了.

那麼該如何解決呢?可能有人會想下Update直接把[TestDB].sys.sysusers user1的SID直接改成跟master.dbo.syslogins user1的SID一樣就行了,想法是對的,不過SQL2005之後為了怕有人亂搞SQL 的system table,所以預設把system table的直接Insert ,update ,delete權限都鎖掉了,所以不能這樣做.
當然你也可以把原資料庫的user1 Drop掉重新再下一次CREATE USER user1 FOR LOGIN user1指令,這樣就會重新對應了,不過通常這招也不一定會成功,因為要drop user得先把相依於user1的物件先砍掉才行,而物件本身如果又相依別的物件的話,就會發現要砍好幾個東西才能把database user砍掉,再加上建回去後所有的權限也要重設,所以也不是一個好解法.

最好的作法是,直接用ALTER USER將user1 user account與login account重新對應就好了,如下:
Use TestDB
GO
ALTER USER user1 WITH LOGIN = user1

下完後各位再去查一下master.dbo.syslogins 與[TestDB].sys.sysusers中的SID,就會發現已經一致了,如此user1就又可以登入存取TestDB了.

備註:也可以用sp_change_users_login 重新對應login account與database user account,不過SQL Server 2008以後建議以ALTER USER WITH LOGIN語法進行處理, sp_change_users_login只是為了保留舊版相容性所以現在還可以使用,未來版本可能就不能用了,請參考MSDN說明.

http://msdn.microsoft.com/zh-tw/library/ms174378.aspx