CODEWITHSUNDEEP
sqlsql-servert-sql
user3115933
user3115933

Reputation: 4443

Deny Select Permission on a set of tables to a specific user

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?

Upvotes: 0

Views: 2717

Answers (3)

Fandango68
Fandango68

Reputation: 4868

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;

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

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

Upvotes: 2

allmhuran
allmhuran

Reputation: 4454

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];')

Upvotes: 3

Related Questions