Fix for ‘The database principal owns a schema in the database, and cannot be dropped’ Error

In SQL Server 2005, if you try to drop a database user by right-clicking the user name and clicking Delete, you may encounter an error as follows.

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

The reason for this is that in SQL Server 2005, if a user is associated with a Schema, then that user has to be first replaced by another user in the schema before you can drop the user from the database.

Fix the Error

In SQL Server 2005 Management Studio, expand the node

Database > yourdatabasename > Security and then click Schemas. On the right pane, you will see a name value pairs list of the schemas and the corresponding owners.

For one or more of the schemas, you should see the user you are trying to drop listed as the owner. For each of these schemas, do the following.

• Right click the schema, and click Properties. In the schema owner box, type the name of the schema, ex. in the following, I would type db_admin over the mynewcommunity_admin.
• Click OK.
• After you do this for all schemas that the user you are trying to drop is the owner of, you should see something like this.

• Now, if you right click the user and click Delete, you should be able to successfully drop the user from the database.