如何从拥有架构的 sql server 2012 中删除用户 [英] How can I delete a user from sql server 2012 who owns a schema

问题描述

我在 Sql Server 2012 中创建了一个新用户，并且偶然"我将他们标记为多个架构的所有者.我想将它们标记为架构的成员，但我在错误的选项卡上！

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

解决方案

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

为了测试这个，我做了以下事情:

创建一个拥有架构的用户和一个测试架构:

使用 tempdb;无需登录即可创建用户 [testuser]；走CREATE SCHEMA [max] AUTHORIZATION testuser;走

尝试删除用户，会失败:

DROP USER [testuser];走

<块引用>

消息 15138，级别 16，状态 1，第 1 行

数据库主体在数据库中拥有一个架构，不能删除.

将架构的所有权转移给其他用户，在本例中为拥有数据库的特殊用户 dbo:

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

现在，删除测试用户，它有效:

DROP USER [testuser];

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

这篇关于如何从拥有架构的 sql server 2012 中删除用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！