BUG: Database Restore May Result in DBO Mismatch if DBO Has Been Changed More Than Once (296437)

The information in this article applies to:

Microsoft SQL Server 2000 (all editions)

This article was previously published under Q296437

BUG #: 353636 (SHILOH_BUGS)

SYMPTOMS

Restoring a database backup that was created while a previous login owned the database may result in a database owner (DBO) mismatch. After the database is restored, the sp_helpdb and sp_helpuser procedures may return different DBOs for that database.

CAUSE

The database backup contains DBO information from before the ownership change, while the master database contains the current ownership information. The restore operation does not reset the database owner.

WORKAROUND

You can work around this problem in the following ways:

Make sure that a full database backup is performed each time that database ownership is changed, and restore only this (or later) backups of the database.
Log in to the SQL Server as a member of the sysadmins group and execute the sp_changedbowner procedure to change the owner of the target database to the correct login.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 2000.

MORE INFORMATION

Steps to Reproduce Behavior

Create a new database:
```
Create Database OwnershipTest
					
```
Execute the following code:
```
USE OwnershipTest
go
sp_helpdb 'OwnershipTest'
go
sp_helpuser
go
						
```
The owner column from sp_helpdb should match the LoginName column of the DBO record from sp_helpuser.

Back up the OwnershipTest database:

BACKUP DATABASE OwnershipTest TO DISK = 'C:\Ownershiptest.bak' WITH INIT

Create a new login and change the DBO to that login:

sp_addlogin 'testowner'
go
USE OwnershipTest
go
sp_changedbowner 'testowner'

Restore the previous backup:

USE master
go
RESTORE DATABASE OwnershipTest FROM DISK = 'C:\OwnershipTest.bak'

Again execute the following code:
```
USE OwnershipTest
go
sp_helpdb 'OwnershipTest'
go
sp_helpuser
go
						
```
The owner column from sp_helpdb will contain "testowner" while the LoginName column of the DBO record from sp_helpuser will contain the name of the original owner.

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

272424 INF: Object Ownership Chain Checking Across Databases Depends on the Login That Is Mapped to the Object Owners

Modification Type:	Major	Last Reviewed:	11/19/2003
Keywords:	kbbug kbpending KB296437

©2003 Microsoft Corporation. All rights reserved.