Dropping a role in a SQL Server database returns Error: 15138 - The database principal owns a schema int he database, and cannot be dropped

Question

I am trying to drop one of the roles in my SQL Server database. I dropped all the members from the role and when I tried to the drop role, I got this error message:

Msg 15138, Level 16, State 1, Line 13
The database principal owns a schema in the database, and cannot be dropped.

Does anyone know why? I checked the Owned Schema and it only had check sign in its own name.

Answer 1

A 15138 error is caused by the fact that the user you are trying to delete owns a schema.

If you run the below query you will get the schema owned by the user.

USE DatabaseName; 
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('UserName');

Let us say it returns 'db_denydatareader' schema. Then you can assign that schema to default user 'dbo' using the below query.

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;