Microsoft SQL Server 2005 DB Restore Issue

After a database backup that was taken from a Microsoft SQL Server 2000 instance was restored to a SQL Server 2005 instance, I experienced an issue where the name of the user that owned the database previously is prepended to all the table names. Check out the image below of the Object Explorer to see what I mean, it's a restored Confluence database; as you can see, "nconfluenceuser" is part of the table names. Usually, this would be "dbo" (DataBase Owner).



This caused an problem for the Confluence instance that attempted to use the database, because none of the queries performed against the database worked - they all failed with an "table does not exist" error.

To get around this issue, I performed the following steps:

1. Created a new database in the Object Explorer.


2. Right-clicked the DB, selected "Tasks", then "Import Data..." - this presented me with the Import and Export wizard.


3. As I was presented with the wizard's welcome screen, I had to hit "Next" to progress to the "Choose a Data Source" screen. Here you select the data source, server and database to import from. As my restored database was on the local machine, the "Data Source" and "Server Name" fields already had appropriate values; all I had to do was select the database from the drop-down list marked "Database".


4. Hitting "Next" took me to the "Destination" screen, which was already filled out correctly, because of how I kicked-off the wizard (via the destination database's context menu).


5. For the next step in the Wizard, I chose the default selection of "Copy data from one or more tables or views" before clicking "Next".


6. Now for the important bit; the "Select Source Tables and Views" step. Here I had to click the "Select All" button, then edit the "Destination" fields for all the tables; removing all the text prior to the table name. After moving down to the next table, the wizard ensured that the edited destination was correctly formatted. See the below pic for an example:
   
   These were the only modification I made in this step, I didn't change any of the other options present


7. I clicked "Next" a couple more times, which allowed me to review the pending transactions, before I clicked "Finish" to finally perform the import; this could take some time, depending on the size of the DB to import and the speed of the machine.


8. After it had finished and I closed the wizard, I was able to see that the newly created DB no longer had the "nconfluence" username prepended to it, but "dbo"; see the below picture.
   


9. My final step was to create a user for Confluence to bind to the database as, granting the account "dbo" privileges over the imported DB.