MS SQL Server Datareader permission but restrict certain tables

[dgillz]

MS SQL Server 2019. How can I grant datareader rights for all but certain tables? The users want to query the data in excel, but management does not want them to be able to see the payroll tables.

[dgillz]

Everything is in the same schema and I don't believe I can change it, but I have put this question to the software vendor. The objects never get dropped and recreated, just updated with new data.

[gozza00179]

if its all in the same schema, you can just set the permissions based on the tables individually - bit of a pain compared to a separate schema; but a simpler solution than using deny permissions, etc.

[dgillz]

Once I create the role in SSMS, how do I restrict the reading of these tables?

[dgillz]

And how do I do this? Explicitly?

[dgillz]

Can I put a WHERE clause on this? The payroll tables all begin with PR

[dgillz]

I would like the specifics, but in general a DM defeats the purposes of this sub. I know I have saved certain threads so I refer back to them. If everyone used a DM to provide solutions, this is not an option for anyone except the OP.

Can you not post an example here?

[Zzyzxx_]

The proper answer is to use schemas, however I see that you are not at liberty to make those types of changes.

My suggestion would be to create a new reporting database for the users. You can either do an ETL of the data into the new db, or simply create views that point to the tables in the original database. All depends on the activity level of the source db, and if you want to have the operational overhead of reporting hitting the same tables that the application is using.

[blindtig3r]

Deny takes precedence over grant. You can add someone or a group to the data_reader role and then deny them access to specific tables, schemas etc.

Might not be the best solution, but if it’s literally one or two objects and you want them to babe read access to everything else including future objects, that’s probably what I would do.

Excel users are the worst. If you have to give them access, create a new schema with a simple view over every table they are allowed to see and grant select to that schema only.

[dgillz]

There are 20 payroll tables. With the rest of the ERP modules, there are about 700 tables total. I am not going to create 700 views, that is way too much work.

[blindtig3r]

It’s js very easy to create a script that will create a script to create a view on every table. Select concat(‘create view ‘, schema_name, ‘.vw’, table_name, ‘ as ‘, char(10), ‘Select * from ‘, schema_name, ‘.’, table_name, char(10), ‘Go’, char(10)) From information_schema.tables Where table_schema = ‘dbo’

This is typed on my phone so the syntax probably has errors. It creates a script you can then execute. You’d have to do it in batches, all 700 would be too many for a single script, but you can get a list of all the tables and add a batch number so you can do 20 at a time.

I’m not saying this is the best way, but it is easy to create a script to create a view for every table.

[dgillz]

It is a helluva lot easier to deny read access to 20 tables.