A few weeks ago, I ran into an issue with a SQL Server 2005 database restored from a Sql Server 2000 backup file. I want to share how I resolved this, hopefully it will be helpful to somebody.

You can restore a Sql Server 2000 database backup to a SQL Server 2005 server. When pages are finished restoring, SQL Server 2005 will attempt to convert and upgrade the database from its previous version. After that, the database is open and almost ready to server customers. Below is a sample output of this restore and upgrade process:

Processed 344 pages for database ‘northwind’, file ‘Northwind’ on file 1.
Processed 1 pages for database ‘northwind’, file ‘Northwind_log’ on file 1.
Converting database ‘northwind’ from version 539 to the current version 611.
Database ‘northwind’ running the upgrade step from version 539 to version 551.
Database ‘northwind’ running the upgrade step from version 551 to version 552.
Database ‘northwind’ running the upgrade step from version 552 to version 553.
Database ‘northwind’ running the upgrade step from version 553 to version 554.
Database ‘northwind’ running the upgrade step from version 554 to version 589.
Database ‘northwind’ running the upgrade step from version 589 to version 590.
Database ‘northwind’ running the upgrade step from version 590 to version 593.
Database ‘northwind’ running the upgrade step from version 593 to version 597.
Database ‘northwind’ running the upgrade step from version 597 to version 604.
Database ‘northwind’ running the upgrade step from version 604 to version 605.
Database ‘northwind’ running the upgrade step from version 605 to version 606.
Database ‘northwind’ running the upgrade step from version 606 to version 607.
Database ‘northwind’ running the upgrade step from version 607 to version 608.
Database ‘northwind’ running the upgrade step from version 608 to version 609.
Database ‘northwind’ running the upgrade step from version 609 to version 610.
Database ‘northwind’ running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 345 pages in 0.335 seconds (8.418 MB/sec).

However, if you try to recreate the same login used in the Sql Server 2000 instance with the same authority, you may get this error, because a restore also restores the users within that database, and login user mapping will not be successful:

User, group, or role ‘test’ already exists in the current database. (Microsoft SQL Server, Error: 15023)

All right, I then attempted to drop that user. I did it through Databases -> Database -> Security -> Users under Sql Server Management Studio. But I got this message:

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

You see, SQL Server 2005 enhanced the concept of schema. As part of the restore and upgrade process, a schema with the same name as the user was created. Since I know all objects are owned by dbo in the original database, I can safely drop the schema (Databases -> Database -> Security -> Schemas through Sql Server Management Studio). And that is exactly what I did. After that, I was able to create the login and user successfully. All is well afterwards.