What to do if the database principal owns a schema in the database, and cannot be dropped?

514 Asked by CamelliaKleiber in SQL Server , Asked on Mar 16, 2023

I am trying to delete a principal from the database but can't because it owns a schema. When I go to edit the user, however, the box to uncheck scheme is blue and unremovable. How can I remove the principal from these schemas?

Answered by Clare Matthews

If the database principal owns a schema in the database, and cannot be dropped, try the T-SQL to do this:

alter authorization

on schema::YourSchemaName

to dbo

go

drop user TheUserYouWantToDelete

go

You can't drop a principal that is a schema owner, so the ALTER AUTHORIZATION changes the owned schema (I used YourSchemaName, but obviously substitute that with the owned schema in your database) to dbo (likewise, you can change ownership to whatever principal you need in your environment). That will allow you to drop the previously-schema-owning user (for example purposes I used TheUserYouWantToDelete, but that'll be the non-owner that you want to drop).

What to do if the database principal owns a schema in the database, and cannot be dropped?

Your Answer