Azure SQL中db_denydatawriter角色用户仍可执行UPDATE的解决方法

一、先排查权限冲突根源 #

用高权限账号登录test数据库，执行以下查询确认用户的实际权限来源：

1. 查看用户直接拥有的数据库权限：

SELECT dp.name AS database_principal, dp.type_desc, 
       perm.permission_name, perm.state_desc, 
       obj.name AS object_name
FROM sys.database_permissions perm
JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE dp.name = 'testuser@decidewaredev.onmicrosoft.com';

重点看是否有state_desc为GRANT的UPDATE/INSERT/DELETE权限。

2. 检查用户所属的数据库角色：

SELECT dp.name AS user_name, dr.name AS role_name
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON drm.member_principal_id = dp.principal_id
JOIN sys.database_principals dr ON drm.role_principal_id = dr.principal_id
WHERE dp.name = 'testuser@decidewaredev.onmicrosoft.com';

确认是否同时属于db_datawriter这类带写入权限的角色。

3. 排查服务器级高权限角色：

SELECT sp.name AS server_principal, sr.name AS server_role
FROM sys.server_role_members srm
JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id
JOIN sys.server_principals sr ON srm.role_principal_id = sr.principal_id
WHERE sp.name = 'testuser@decidewaredev.onmicrosoft.com';

服务器级角色（如sysadmin）会直接覆盖数据库级的DENY限制。

二、针对性修复配置 #

1. 撤销直接授予的写入权限
   如果查询发现用户有直接授予的写入权限，执行：

REVOKE UPDATE ON DATABASE::test FROM [testuser@decidewaredev.onmicrosoft.com];
REVOKE INSERT ON DATABASE::test FROM [testuser@decidewaredev.onmicrosoft.com];
REVOKE DELETE ON DATABASE::test FROM [testuser@decidewaredev.onmicrosoft.com];

2. 清理冗余角色，保留必要权限
   如果用户属于db_datawriter或其他写入角色，先移除：

EXEC sp_droprolemember 'db_datawriter', 'testuser@decidewaredev.onmicrosoft.com';

再确认用户仅加入目标角色：

EXEC sp_addrolemember 'db_datareader', 'testuser@decidewaredev.onmicrosoft.com';
EXEC sp_addrolemember 'db_denydatawriter', 'testuser@decidewaredev.onmicrosoft.com';

3. 移除服务器级高权限角色
   若用户属于sysadmin等服务器角色，必须移除：

EXEC sp_dropsrvrolemember 'testuser@decidewaredev.onmicrosoft.com', 'sysadmin';

4. 验证权限生效
   让用户重新登录后执行测试语句：

UPDATE 你的表名 SET 列名 = 测试值 WHERE 1=1;

正常会返回权限拒绝错误，说明配置生效。

三、额外注意点 #

• 确认Entra用户与SQL数据库用户的映射唯一，避免重复映射导致权限混乱：

SELECT name, type_desc, authentication_type_desc FROM sys.database_principals WHERE name = 'testuser@decidewaredev.onmicrosoft.com';

• 如果用户是通过Entra组映射的，需同步检查组的权限配置，避免组权限覆盖用户单独的DENY设置。

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