How can I delete a user from sql server 2012 who owns a schema

Question

I created a new user in Sql Server 2012 and "by accident" I marked them as owner of multiple schemas. I meant to mark them as members of the schema but I was on the wrong tab!

Because they are now owners of the schema I can't unselect the ownership and I can't delete the user either. How can I undo my mistake?

Answer 1

You must transfer ownership of the schema to some other user, probably dbo, prior to removing the user:

To test this, I did the following:

Create a user to own the schema, and a test schema:

USE tempdb;
CREATE USER [testuser] WITHOUT LOGIN;
GO
CREATE SCHEMA [max] AUTHORIZATION testuser;
GO

Try to drop the user, which will fail:

DROP USER [testuser];
GO

Msg 15138, Level 16, State 1, Line 1

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

Transfer ownership of the schema to some other user, in this case the special user, dbo, which owns the database:

ALTER AUTHORIZATION ON SCHEMA::[max] TO dbo;
GO

Now, drop the test user, which works:

DROP USER [testuser];