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

by Allan Svelmøe Hansen February 05, 2009 18:57

Dropping a user which owns a schema will result in the error message “The database principal owns a schema in the database, and cannot be dropped”.

To solve this you can assign the schema back to its base principal with the ALTER AUTHORIZATION message, or it can be moved to another user/principal.
If for example user Test1 owns the db_owner schema and you want to drop Test1 but don’t have another user to move the schema to you can type:

ALTER AUTHORIZATION ON SCHEMA::db_owner TO db_owner  
(or for example db_datawriter if that was the case and so on)

If you wanted to move the schema to another user the syntax would be:

ALTER AUTHORIZATION ON SCHEMA::<theSchemaTO userName  

Bookmark and Share DotnetKicks dotnetshoutout

Comments

3/19/2009 9:51:12 PM #

Thanks very much for this helpful bit of information. Smile

Kevin United States

6/12/2009 9:14:39 AM #

Awesome, thanks!

Lorien United Kingdom

11/27/2009 6:32:31 AM #

Thanks for the great post.

david mandy

2/17/2010 8:08:57 PM #

nice suggestion

ketir7

2/18/2010 10:33:51 PM #

Thank you very much!!!

Alex Chile

2/27/2010 4:56:03 PM #

Thanks! My problem is solved!I searched for hours and could not find this solution.

Cornelis Belgium

3/30/2010 5:21:15 PM #

Great, just what I needed.

Bryan W United States

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Powered by BlogEngine.NET 1.6.1.0
Theme by Mads Kristensen | Modified by Mooglegiant

About:
Allan Svelmøe Hansen

My real name is Allan Svelmøe Hansen.
I live in Denmark, where I work as a developer for hedal:kruse:brohus using SQL Server and the .NET framework since 2004.
My primary fields of expertise is back-end data integration, database design and optimization.


       View Allan Svelmøe Hansen's profile on LinkedIn     

Disclaimer

The opinions expressed herein are my own personal opinions and thoughts and does not represent my employers view in any way, nor are my results guaranteed for all situations.
Content is presented "as is", with no warranty.