Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

This is a typical issue faced by every Database guy if they are beginner to SQL DB world. This appears when we try to delete a database user who's owner of the database schema and SQL DB does not allow to delete the user if the user owns a schema.

Resolution to this is, a) Find out the schema or schema's owned by that user.

                                      b) Assign those schema to some other user

                                      c) Delete the user.

This can be done in 2 ways 1. Using SQL Script

                                                   2. Using Management Studio.

Using SQL Script

SELECT name FROM  sys.schemas WHERE principal_id = USER_ID('DBUser')

Run another command to assign the schema to another user

   

ALTER AUTHORIZATION ON SCHEMA::NameofSchema TO DBUser2 

GO

DROP USER DBUser

Using Management Studio

Step 1 : Select the User name and Open Property Window

Step 2 : Select the Schema Property viewed in above screen

Step 3 : Assign the Schema to another user by clicking Search and Browsing appropriate new user.

The most recommended is to assign to "dbo".

Step 4: Now if you see the new user property you can see the Schema is owned by the new user.

Now you can delete the user which you are intend to.