SQL嵌套执行问题：varchar限制下权限拒绝语句执行报错

我懂你遇到的痛点了——因为varchar有8000字符的长度限制，你不想把生成的用户列表存到变量里，想直接拼接并执行DENY权限语句，但之前尝试的嵌套EXEC写法都因为语法问题报错了。

为什么之前的写法会报错？ #

SQL Server里，EXEC（或sp_executesql）是执行语句，不能作为表达式嵌入到字符串拼接操作中，所以你之前的exec('deny ... ' + (exec @x))这类写法都会触发语法错误，这是SQL Server的语法规则限制。

可行的解决方案 #

下面给你两种直接生成并执行DENY语句的方法，都不需要担心长度限制：

方法1：直接拼接用户列表到DENY语句中（SQL Server 2017+）

利用STRING_AGG直接生成用户列表字符串，拼接到DENY语句里一次性执行：

EXEC sp_executesql N'
DENY SELECT ON [schema].[TABLE] TO ' + 
(SELECT STRING_AGG(CONCAT('[', DatabaseUserName, ']'), ', ')
 FROM (
     SELECT DP1.name AS DatabaseRoleName, 
            ISNULL(DP2.name, ''No members'') AS DatabaseUserName 
     FROM sys.database_role_members AS DRM 
     RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id 
     LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id 
     WHERE DP1.type = ''R''
 ) AS txt);

这里用sp_executesql代替EXEC是更安全的做法，同时STRING_AGG返回的结果会直接拼接到DENY语句中，不需要中间变量存储。

方法2：兼容旧版本SQL Server（2016及以下）

如果你的SQL Server版本不支持STRING_AGG，可以用FOR XML PATH来拼接用户列表：

EXEC sp_executesql N'
DENY SELECT ON [schema].[TABLE] TO ' + 
(SELECT STUFF((
     SELECT '', [' + ISNULL(DP2.name, 'No members') + ']'
     FROM sys.database_role_members AS DRM 
     RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id 
     LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id 
     WHERE DP1.type = ''R''
     FOR XML PATH(''), TYPE
).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''));

额外提示 #

如果你担心用户列表过长（虽然NVARCHAR(MAX)支持存储最多2GB的内容，一般足够），可以拆分多条DENY语句执行，不过大多数场景下上面的方法已经足够解决问题。

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