7

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

BrianLegg
  • 1,658
  • 3
  • 21
  • 36
  • 2
    `ALTER AUTHORIZATION ON SCHEMA::db_datareader TO [some_other_user];` (Also, this is why we write DDL scripts that we can validate, store in source control, etc. The only way to verify what you did pointing and clicking through a UI is to record some kind of screencast.) – Aaron Bertrand Oct 08 '15 at 20:21

1 Answers1

10

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];
Hannah Vernon
  • 3,367
  • 1
  • 26
  • 48