Fix for 'The database principal owns a schema in the database, and cannot be dropped' Error in SQL Server 2005
I was trying to restore one database backup; which I successfully did (I'm using SQL Server 2005), and want to delete the users of the database which are restored along with the DB; I received this error which says "The database principal owns a schema in the database, and cannot be dropped".
While I googled the error, I found that the user which i want to delete is called "Database Principal" is having "DB_Owner" Role selected. That means the Database Pricipal I want to delete owns the Schema. One solution was to delete the Schema and then delete the user, :-) which I am obviously not going to do. Another Solution was to change the DB_Owner to some another "Database Principal"; let say to "dbo". I did it accordingly and then I was able to delete the user/Database Pricipal successfully.
To change the DB_Owner to some another "Database Principal"; simply Drill Down to your Database in Sql Server Management Studio and further more Drill Down to your_DB_Name > Schemas > db_owner > right click > select properties. You would find the name of the "Database Principal" that you want to delete. Change this to some another "Database Principal"; for Example, to "dbo".
You need to make the same changes to all the schemas where the "Database Principal" is having/owning the role. For Example, if it is having "db_owner, db_datareader, db_datawriter" roles; then you have to make the above said changes for all these 3 schemas.
You would find more details about this error and its solution at this page > Error Solution: Fix for 'The database principal owns a schema in the database, and cannot be dropped' Error in SQL Server 2005