Microsoft SQL Server Tips Series: Database Ownership

Ray HuangHere is a very unique error that customers might encounter when restoring their database:

The database principal owns a schema in the database, and cannot be dropped.

And so far, this error can only be created when the customer creates a database login with the same name on their local SQL Server Instance that matches the primary login of their database here at DiscountASP.NET and makes it the db_owner of the database schema.

How to Create the Problem

As always, I’ll begin by attaching a copy of the AdventureWorks database to my local SQL Server instance.  Next, I will create a SQL Server and database login with the same name as the primary login of the database here at DiscountASP.NET.   To do this:

1)      Right click on the Security folder at the SQL Server level and select New -> Login…

2)      In the Login window under the General section, type in the Login name to be exactly the same as the login name for the primary user of your database here at DiscountASP.NET.

3)      Check SQL Server authentication and enter a password.

SQLUser

4)      Now go to the User Mapping section, check the box next to the AdventureWorks database and map it to the same username on the database level login and click OK.

UserMapping

5)    Now, right click on the login name at the database level and select Properties.

6)    In the Database User window under Owned Schemas, check the box next to db_owner.

DBOwner

7)    Now, if you make a backup locally, upload the .bak file, and try to restore it using the tools in the DiscountASP.NET Control Panel, it will generate the error above on the back end.

The Solution

The fix for this is really simple.  You just need to go back to your local SQL Server instance and right click on the dbo database user and select Properties.  Change the context to Owned Schemas, check db_owner and select OK.

dbo

Now if you try to make a backup, upload it, and restore it, it will succeed.

Leave a Reply

Your email address will not be published. Required fields are marked *