I recently ran into an issue where a development manager was creating a new role in SQL for his data warehouse initiative and cube building. He granted the role ownership of a newly created schema then changed his mind on what he wanted regarding a few items.

I provided the explanation to him that I had ran into this in the past and provided the following so that he could transfer the owned schema to another user or role then drop his role he created.

If you try to drop the role while the schema is owned by it SQL will produce the error, “The database principal owns the schema in the database, and cannot be dropped”.

In this particular instance we only had one schema this role was tied to but if a user or role was tied to many you can run the below query to determine what all schema’s are tied to the user:

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘testname’)

The next query will transfer the schema’s you want to move over to dbo for example so you can remove the role then re-assign out as needed:

ALTER AUTHORIZATION ON SCHEMA::SchemaName TO dbo

As always I recommend testing locally or in a test environment prior to doing any type of prod related activity.