Stopbyte

SQL Server: "The database principal owns a schema in the database, and cannot be dropped"

Database Development SQL Server
#1

How can I remove a database principal that owns schema’s in my SQL Server?

I’ve already removed the Login successfully, but when i attempted to remove the User SQL Server (SQL Server Management Studio v2014) returned the following error:

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

Any help would be appreciated!

2 Likes
#2

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

The problem is as the error message states the User you are trying to delete is an owner of at least one Schema in your SQL Server. Thus it cannot dropped unless the DB Schema owners are changed explicitly.

So since you are using SSMS (SQL Server Management Studio) to drop the User here are the steps you should follow to get that done correctly:

  1. Here is the User we are going to Drop (Delete) in this example (named CC_User):
    SQL Server Management Studio 2014 Object Explorer

  2. Under Schemas you will find all your Schema’s that belong to the Database in question:

    SQL Server Management Studio 2014 Object Explorer Schemas
    Pasted image400×621 18.8 KB

  3. Go through all Shemas double clicking each.

  4. When a Schema is double clicked (e.g. db_datareader), this will show the following Window:

    SQL Server Management Studio 2014 Schema panel
    Pasted image690×625 16.5 KB


    The window above, shows the “Schema Owner” change that to another owner, if you don’t know which set it to default owner which is named the same as the schema itself for all schemas (by default).

e.g. db_datareader default schema owner is “db_datareader”.

  1. Remember to go through all Schemas in your DB and check if the user owns any of them, if it does replace it with another owner. The User Drop wont work unless the user ownership is removed on all schemas.

Hope this helps solving your problem.

2 Likes
#3

Your answer is good, but not good enough for my case, it really does the Job when using SQL Server Management Studio itself,
But unfortunately I also need to remove Users programmatically from the back-end if possible.

1 Like
#4

In that case here is a very easy to use Transact-SQL script that you can use from any backend you want:

1. First; Changed the owned Schema (just the same as SSMS suggested by @isoftech above) using the ALTER AUTHORIZATION instruction something like this:

ALTER AUTHORIZATION
ON SCHEMA::TheSchemaToAlter
TO TheNewOwner
GO

The instruction above changes the ownership of the Schema named "TheSchemaToAlter" to the owner named "TheNewOwner"

2. Afterwards you can safely drop the user using DROP USER instruction:

DROP USER TheUserYouWantToDelete
GO

This solution should work perfectly for you, Remember that an User might have ownership on multiple schemas so make sure to Alter Authorization for all those schemas.