Tuesday, October 16, 2012

The database principal owns a schema in the database, and cannot be dropped - Drop User


I got the below error while removing user from a database.

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

There is a orphan user in my database, no schemas or no objects were defined by that user. No login is associated with it, even though some times we could n't delete it.

For suppose you have a user with name "kalyan" owns db_owner schema, to drop the user first you need to move that schema to another user. If you dont have another user to move schema then use the following syntax to do that.

use database name
Alter Authorization On Schema::db_owner TO db_owner

If you have datareader schema then use datareader in place of db_owner. Now drop the user it will allow you to drop the user from the database.

No comments: