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.
20 people commented!
Leave a Reply
-
-
Subscribe
Search
Hi,
This solution is for users. If we got the same error with database roles, how can we fix this?
Thank you
Gud one dear,
Looks so simple , if not done it created huge problems
Thanks, it’s work for me
Thanks for your solution. It works well for me :)
Thank you very much! :)
If you are unsure of the schema (in my case it wasn’t the schema i had a problem with, but db_owner) – then run this query and look at the SCHEMA_OWNER column to look for the user you’re trying to delete. Then change the owner of that schema to something (dbo for example) and you will be able to delete the user.
select * from INFORMATION_SCHEMA.SCHEMATA
Great post this, thanx for the help guys.
Thanks… this worked for me as well.
it worked for me, thanks a lot
Thanks allot…It worked for me.
Excellent!
THanks guys that was a really helpful bit of advice, changed th owner of the schema and then could delete the user from the d-base
This is a very helpful post, I was looking for this information. Just so you know I found your website when I was searching for blogs like mine, so please check out my site sometime and leave me a comment to let me know what you think.
Thank you!
Thank dear,
This resolved my problem. Thanks a lot.
Excellent Solution!
thanks issue solved
thanks a lot…..
its awesome ……………
Megha
Did not work for me for the reason that according to the query SELECT name FROM sys.schemas WHERE principal_id = USER_ID(‘myuser’) The user does not own any schema. So why am I getting this error and how can I delete?
Thank you, this worked a treat for me and you solved my mouse from being smashed :-)
It’s really good…