Microsoft KB Archive/296437

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

Article ID: 296437

Article Last Modified on 11/19/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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.



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' </li>  Again execute the following code: USE OwnershipTest go sp_helpdb 'OwnershipTest' go sp_helpuser go The owner column from sp_helpdb will contain &quot;testowner&quot; while the LoginName column of the DBO record from sp_helpuser will contain the name of the original owner. </li></ol>

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

Additional query words: restore dbo mismatch sp_helpdb sp_helpuser

Keywords: kbbug kbpending KB296437

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.