拒绝特定用户对一组表的"选择权限"

Question

我正在使用SQL Server 2014。我需要拒绝特定用户对一组表的SELECT权限。

我有以下代码可以完成这项工作，但我需要为每个相关的表运行它：

USE [MyDatabase]
DENY SELECT ON [mktg_Rating] TO [xxxxxUser]

假设我必须拒绝特定用户对以"开头的所有表的选择权限；mktg_&"；，我如何重新编写上面的代码来一次性完成这项工作？

Answer 1

如果所有表都位于自己的模式中，而该模式中没有其他对象，则可以使用schema::yourSchemaNameHere拒绝对模式本身进行选择

除此之外，没有办法在一句话中做到这一点。您不能使用通配符等。但您可以通过使用动态管理视图做一些有趣的事情，让SQL为您生成命令：

select concat('deny select on ', s.name, '.', t.name, ' to [xxxxxuser];')
from   sys.tables  t
join   sys.schemas s on s.schema_id = t.schema_id
where  t.name like 'mktg[_]%'

运行此查询以生成所需的语句，然后将输出复制回查询窗口并一起运行所有语句。

如果你有一些时髦的名字，请添加对quotename()的调用。。。

select concat('deny select on ', quotename(s.name), '.', quotename(t.name), ' to [xxxxxuser];')

Answer 2

按照allmhuran建议的动态SQL解决方案(我建议+1！(，假设SQL Server 2017或更高版本，您可以利用string_agg为您创建SQL：

DECLARE @Sql nvarchar(max);
SELECT @Sql = 'DENY SELECT ON '+ 
(
SELECT STRING_AGG(QUOTENAME(name), ' TO [xxxxxuser]; DENY SELECT ON ')
FROM sys.Tables
WHERE name LIKE 'mktg%'
) + ' TO [xxxxxuser];'
-- When using dynamic SQL, print is your best friend.
PRINT @Sql;
-- Once you've seen that the SQL is Ok, go ahead and unremark the `Exec` to execute it.
--EXEC(@Sql)

查看db<gt；fiddle

Answer 3

好的老CURSOR总是对我有用：

DECLARE tblCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE 'mktg%';
OPEN tblCursor;
FETCH NEXT FROM tblCursor INTO @objName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('DENY ALL ON [dbo].' + @objName + ' TO [xxxxxuser];');
FETCH NEXT FROM tblCursor INTO @objName;
END
CLOSE tblCursor;
DEALLOCATE tblCursor;