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