Learning: recover the isolated database DBO

After the backup file is restored on another computer in the SQL Server database, it is found that DBO's Logon account is empty in the Enterprise Manager. After searching, it is found that DBO is isolated, the DBO user has a Logon account, but the SID of the user in the sysusers system table does not match the SID of the Logon account in the syslogins system table.

You can use the following command to check whether DBO Sid is isolated:

1 Select U. Name As "Name ", Isnull (L. Name, ' DBO is unmatched ' ) As "Matched login"
2
3 From Sysusers u
4
5 Left   Join Master. DBO. syslogins L On U. Sid = L. Sid
6
7 Where U. Name =   ' DBO '
8
9

The cause of this problem is in KB.ArticleInstructions in: http://support.microsoft.com/kb/305711/zh-cn

However, when I use the method in KB above:

1 Exec Sp_change_users_login ' Update_one ' , ' DBO ' , ' Nt authority \ System '

Tip: the process will be terminated. 'Dbo' is a disabled value for the login name parameter during this process.

Check msdn immediately to know that sp_change_users prohibits DBO, guest, or information_schema users from being changed.
What should we do? Manually update the SID of DBO. The process is as follows:

Use Master
Select   *   From Syslogins

Find the SID of the user to be set

Use the following statement to update

1 Sp_configure ' Allow updates ' , 1  
2 Reconfigure   With Override
3 Go
4
5 Update Sysusers Set Sid = Sid Information Where Name = ' DBO '
6 Go
7
8 Sp_configure ' Allow updates ' , 0  
9 Reconfigure   With Override
10 Go  
11
12

Okay.

Article by: http://www.cnblogs.com/baoposhou/archive/2006/01/16/318212.html