恢复被孤立的数据库dbo
SQL Server数据库,备份文件在另一台机算机上还原后,发现在企业管理器中表现为dbo的登录帐号为空,经过查找发现是dbo被孤立,dbo 用户具有显示登录帐户,但该用户在 sysusers 系统表中的 SID与该登录帐户在 syslogins 系统表中的 SID 不匹配。
可以用以下查看dbo的sid是否孤立:
1
SELECT u.name AS "Name", ISNULL(l.name, 'dbo is unmatched') AS "Matched Login"
2![](/Images/OutliningIndicators/None.gif)
3
FROM sysusers u
4![](/Images/OutliningIndicators/None.gif)
5
LEFT JOIN master.dbo.syslogins l ON u.sid = l.sid
6![](/Images/OutliningIndicators/None.gif)
7
WHERE u.name = 'dbo'
8![](/Images/OutliningIndicators/None.gif)
9![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
出现此问题的原因在这篇KB文章中有说明:http://support.microsoft.com/kb/305711/zh-cn
但是当我使用上面KB中的方法:
1
EXEC sp_change_users_login 'Update_One', 'dbo', 'NT AUTHORITY\SYSTEM'
![](/Images/OutliningIndicators/None.gif)
提示:将终止此过程。对于此过程中的登录名参数,'dbo' 是禁止使用的值。
马上查看msdn,才知道sp_change_users是禁止更改dbo,guest 或 INFORMATION_SCHEMA用户的
那怎么办呢?自己手动更新dbo的SID吧。过程如下:
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
找出要设定的用户的SID
然后使用以下语句更新
1
SP_CONFIGURE 'allow updates',1
2
RECONFIGURE WITH OVERRIDE
3
GO
4![](/Images/OutliningIndicators/None.gif)
5
UPDATE sysusers SET sid=SID信息 WHERE NAME='dbo'
6
Go
7![](/Images/OutliningIndicators/None.gif)
8
sp_configure 'allow updates', 0
9
reconfigure with override
10
Go
11![](/Images/OutliningIndicators/None.gif)
12![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
搞定,呵呵。