07 January 2009

How To: Drop a user that owns a schema

SQL Server returns the error

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

when a user is being dropped that owns a schema in the database.

The delete the user go to Sql Server Management Studio, expand your database -> Security and press on Schemas. In the Object Explorer Details (if not visible go to the View Menu e select Object Explorer Details) you can see a list of the schemas and the owners.

Now locate the schema(s) the user you want to delete is the owner, right click and select properties. In the General you can see the schema owner, change it to the new owner (dbo for example).

When the user you want to delete has no schemas owned you can delete it.

5 comments:

Anonymous said...

Very usefull,
Thanks!!

Anonymous said...

Excelent!

Anonymous said...

Perfect - exactly what I needed to know.

Anonymous said...

great...
I need this way

Anonymous said...

Thanks , useful