sql-server - 无法从 db_owner-group 中删除用户

Question

TL;DR - 出于某种原因由脚本创建的用户会自动添加到 db_owner 组。但我无法使用脚本删除它们。

所以我编写了一个脚本来将新的 Windows 用户添加到我的数据库中，但由于某些原因，默认情况下它们会自动添加到 db_owner 组中。服务器上的所有者是“sa”，因此服务器上已经设置了一个所有者。

这是我使用以下脚本添加新用户时发生的情况:

CREATE LOGIN [Windows\user1] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

USE [dbName]
GO
IF NOT EXISTS (SELECT [name] FROM [sys].[database_principals] WHERE [name] = N'Windows\user1')
BEGIN
    CREATE USER [Windows\user1] FOR LOGIN [Windows\user1]
END
EXECUTE sp_addrolemember 'Admin', 'Windows\user1'
GO
EXECUTE sp_addrolemember 'db_datareader', 'Windows\user1'
GO
EXECUTE sp_addrolemember 'db_datawriter', 'Windows\user1'
GO

但是当我运行以下代码时，它显示“1 行受影响”。但用户仍在 db_owner 组中。

ALTER ROLE [db_owner] DROP MEMBER [Windows\user1]

我现在可以删除它的唯一方法是使用 SSMS 中的 UI 转到用户的属性并取消选中它。有哪些方法可以解决这两个问题？

显然这是不正确的。他们甚至不会以这种方式被移除......

更新(1):我设法将其缩小到它似乎与自定义角色“Admin”有关的事实 - 当用户添加到此角色时，它会在运行以下脚本时自动添加到 db_owner。

EXECUTE sp_addrolemember 'Admin', 'Windows\user1'

仍然没有设法找出原因。

Answer 1

预测:如果您运行以下查询，则结果将为 1:

select   count(*)
from     sys.database_role_members   rm
join     sys.database_principals     rp   on rp.principal_id = rm.role_principal_id
join     sys.database_principals     mp   on mp.principal_id = rm.member_principal_id
where    rp.name = 'db_owner'
         and mp.name = 'Admin';

如果是这种情况(我确信是这样)，那么 Admin 角色就是 db_owner 角色的成员。所以 admin 的所有成员都是 db_owner 的成员。

然后 user1 被授予 admin 角色的成员身份。我们现在形成一个三段论:

所有管理员都是db_owners。
user1 是 admin。
因此 user1 是一个 db_owner。

尝试从 db_owner 角色中删除 user1 无效，因为他们不是 db_owner 角色的直接成员。他们是 admin 角色的成员，而该角色又是 db_owner 角色的成员。

如果您想删除他们的 db_owner 成员资格，您必须通过将他们从 admin 角色中删除来实现。