主页 / dba / 问题 / 265712
Accepted
user1930901
Asked: 2020-04-24 11:42:18 +0800 CST

为过程引用的所有表生成 GRANT

  • 772

我的数据库中有几个存储过程，每个存储过程都从几个表中选择。我需要构造一个语句，我可以在其中给它一个存储过程，并为该存储过程使用的所有表获取授权选择语句。我需要这个，因为我的过程使用 sp_executesql，因此用户需要对表的显式权限，因为 sp_executesql 使得对存储过程的执行权限不够（您可能知道用户也需要对该 SP 使用的表的选择权限）。

到目前为止，我已经完成了以下动态 SQL 语句，但是，它仅在存储过程仅从一个表中选择时才有效。我完全理解，由于 where 子句中的子查询，它不再适用。

select 'GRANT SELECT ON [' + schema_name(schema_id) + '].[' + name + '] TO [User1]'
from sys.tables 
where name like (SELECT distinct t.name
FROM sys.sql_dependencies d 
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
where P.NAME LIKE 'myproc')

SP 使用超过 1 个表时出错：

子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。

存储过程使用的每个表所需的输出：

GRANT SELECT ON [dbo].[table1] TO [User1]

还有另一种方法可以实现这一目标吗？

sql-server stored-procedures

1 个回答

  1. Best Answer

    错误信息是正确的。您不能LIKE反对查询的输出，除非您以某种方式明确将该输出限制为一行（这不是您想要做的）。只需执行连接...您可以清除重复项，但没有理由（如果可能，两次授予同一张表不会伤害任何东西）。也请使用QUOTENAME()而不是手动添加[和]自己。

    DECLARE @sql nvarchar(max) = N'';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT SELECT ON ' 
  + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N'] TO [User1];'
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.sql_dependencies AS d
ON d.referenced_major_id = t.object_id
INNER JOIN sys.procedures AS p
ON p.[object_id] = d.[object_id]
WHERE p.name = N'myproc';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

    请记住，sys.sql_dependencies不一定会捕获所有内容，例如，如果存储过程使用动态 SQL，它无法解析文本以查找所有表引用，或探索所有可能的排列。

    • 1

相关问题