CODEWITHSUNDEEP
sql-serversql-server-2012
BrianLegg
BrianLegg

Reputation: 1700

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

Upvotes: 7

Views: 19998

Answers (2)

Ashok Yadav
Ashok Yadav

Reputation: 21

Msg 15138, Level 16, State 1, Line 1 The database principal owns a schema in the database, and cannot be dropped.

Syntax:

USE [DatabaseName]
GO
ALTER AUTHORIZATION ON SCHEMA::[Schema_Name] TO [dbo]
GO

Example:

USE [TestDB]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [dbo]
GO

Upvotes: 0

Hannah Vernon
Hannah Vernon

Reputation: 3472

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

Upvotes: 12

Related Questions