sql-serversql-server-2012

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

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?

Schema Ownership

Solution

  • 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];