SQL Server脚本创建用户自动加入db_owner组 无法通过脚本移除

核心摘要：出于某些原因，通过脚本创建的用户会自动加入db_owner组，但无法通过脚本将其从该组移除。

编写脚本向数据库添加新Windows认证用户时，用户会默认自动加入db_owner组。当前服务器所有者为sa，已配置明确的服务器所有者。
问题截图1
问题截图2

使用以下脚本添加新用户即可复现问题：

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

尝试运行如下命令移除用户的db_owner角色时，系统提示“1 行受影响”，但刷新后用户仍然留在db_owner组中：

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

最初曾以为只能通过SSMS可视化界面进入用户属性页取消勾选才能移除db_owner权限，后续验证发现该认知错误：通过SSMS界面操作同样无法移除该用户的db_owner角色。

已定位到问题和自定义角色Admin直接相关：只要执行如下脚本把用户加入Admin角色，用户就会自动出现在db_owner组成员列表中，暂未找到该现象的根本原因。

EXECUTE sp_addrolemember 'Admin', 'Windows\user1'

该问题是数据库角色嵌套继承导致的：你创建的自定义角色Admin本身就是db_owner固定数据库角色的成员，SQL Server中角色的权限会向下传递给所有子成员，因此只要用户被加入Admin，就会自动继承db_owner的所有权限，同时在db_owner成员列表中显示。这种继承来的角色成员身份无法直接删除，不管是用脚本还是SSMS界面操作都不生效。

按以下步骤操作即可修复：

1. 执行如下查询确认Admin角色的归属，验证问题根因：

   USE [dbName]
   GO
   SELECT 
       dp1.name AS 上层角色名,
       dp2.name AS 成员名
   FROM sys.database_role_members drm
   JOIN sys.database_principals dp1 ON drm.role_principal_id = dp1.principal_id
   JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
   WHERE dp2.name = 'Admin'
   

   如果返回结果中存在db_owner的记录，即可确认是角色嵌套导致的问题。
2. 将Admin角色从db_owner组中移除：

   ALTER ROLE [db_owner] DROP MEMBER [Admin]
   

3. 根据业务实际需要的最小权限，单独给Admin角色授予对应操作权限，不要直接将自定义角色加入db_owner高权限组。
4. 操作完成后刷新用户权限列表，即可看到加入Admin角色的用户已经不再属于db_owner组。

内容的提问来源于stack exchange，提问作者Danieboy