如何从拥有架构的sql server 2012中删除用户

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!

我在Sql Server 2012中创建了一个新用户并且“偶然”我将它们标记为多个模式的所有者。我打算将它们标记为模式的成员，但我在错误的选项卡上！

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?

因为他们现在是架构的所有者，所以我无法取消选择所有权，也无法删除用户。我怎样才能解除我的错误？

1 个解决方案

#1

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

在删除用户之前，您必须将架构的所有权转移给其他用户，可能是dbo：

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

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:

将架构的所有权转移给其他用户，在这种情况下是拥有数据库的特殊用户dbo：

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

Now, drop the test user, which works:

现在，删除测试用户，该用户有效：

DROP USER [testuser];

#1