0

I am using SQL Server 2014. I need to deny SELECT permission for a specific user on a set of tables.

I have the following code which will do the job but I need to run it for each relevant table:

USE [MyDatabase]

DENY SELECT ON [mktg_Rating] TO [xxxxx\User]

Assuming I have to deny select permission to a specific user on all tables starting with "mktg_...", how can I re-write the above code to do this in one go?

user3115933
  • 4,303
  • 15
  • 54
  • 94
  • Beware that future tables with mktg_ will be accessible to the user, unless you constantly check for these tables and execute the DENY again on a maintenance job. Also, if you have a few users to restrict, it would be better to use roles. – EzLo Jul 09 '20 at 07:44

3 Answers3

3

If all of the tables live in their own schema, and no other objects are in that schema, you could deny select on the schema itself using schema::yourSchemaNameHere

Other than that, there's no way to do it in a single statement. You can't use wildcards and so on. But what you can do is have SQL generate the commands for you, by doing some fun stuff with the dynamic management views:

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

Run this query to produce the statements you need, then copy the output back into a query window and run all of them together.

Add in calls to quotename() if you have some funky names...

select concat('deny select on ', quotename(s.name), '.', quotename(t.name), ' to [xxxxx\user];')
allmhuran
  • 4,154
  • 1
  • 8
  • 27
2

Going along with the dynamic SQL Solution suggested by allmhuran (+1 from me!) - and assuming SQL Server 2017 or higher, you can leverage string_agg to create the sql for you:

DECLARE @Sql nvarchar(max);

SELECT @Sql = 'DENY SELECT ON '+ 
(
    SELECT STRING_AGG(QUOTENAME(name), ' TO [xxxxx\user]; DENY SELECT ON ')
    FROM sys.Tables
    WHERE name LIKE 'mktg%'
) + ' TO [xxxxx\user];'

-- 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)

See a live demo on db<>fiddle

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • Ooh. I've been using dynamic SQL and DMV's for *years* to do this kind of thing (as have we all no doubt), and I never even thought about using string_agg on our newer instances for this kind of work. It's funny how blind you can be to stuff outside your habits. – allmhuran Jul 09 '20 at 07:57
  • @allmhuran on older instances (pre 2017, that is) you can use the `stuff+for xml` trick to do the same. It's a bit more long-winded, but it gets the job done :-) – Zohar Peled Jul 09 '20 at 08:13
  • Oof, using xml in cases where there is literally *any* remotely reasonable option other than xml? No thank you sir! :P – allmhuran Jul 09 '20 at 09:54
  • @allmhuran you're not really using xml, you're just aggregating a string... check [this for an example.](https://stackoverflow.com/a/41788610/3094533) – Zohar Peled Jul 09 '20 at 10:13
  • I refuse. I'm not saying it's wrong. I just refuse in the same way that I would refuse a perfectly good hamburger if pizza was an option. – allmhuran Jul 09 '20 at 10:16
  • 1
    @allmhuran well, that really depends on both the pizza and the hamburger, but I get your drift :-) – Zohar Peled Jul 09 '20 at 10:20
0

Good old CURSOR always works for me:

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 [xxxxx\user];');
    FETCH NEXT FROM tblCursor INTO @objName;
END
CLOSE tblCursor;
DEALLOCATE tblCursor;
Fandango68
  • 4,461
  • 4
  • 39
  • 74