Grant all tables (except 1) Read permission on a Role while not knowing which tables there are every day

Question

Every day in our datawarehouse (that will be dynamically changing) the tables are dropped and rebuilt. Also is it possible that some developer in our organisation will create more tables in that database. Because of that I can not give permissions to the database that are persistent.

Question: I want to make some kind of a job that runs every day, that lists all the table names (that are existing at that time) in a database like 'Select * FROM sys.tables' Then I want the tables names as an input value to a script that runs trough all table names and places them in a script like :

GRANT SELECT TO [Tablename1] TO [ROLE_READALLTABLES Except 1 table],
GRANT SELECT TO [Tablenaam2] TO [ROLE_READALLTABLES Except 1 table] 

and so go on in a loop until all existing tables are readable. So all tables (except 1 table ) in the entire database should get the GRANT SELECT permission.

I have looked around all the related answers, but I cannot seem to get a good idea how to get this to work. I hope someone can help me with this.

UPDATE I use Microsoft SQL Server 2014, and I work through SQL Management Studio 2014 UPDATE 2 : There is one exception. This table has schema [dbo]. like all other tables

Answer 1

You can use the db_datareader role to grant access to all tables generally, then a specific role with a DENY rule to exclude access to the one table that's the exception.

The steps would be roughly like this:

1) Create your "Read all except 1 role":

CREATE ROLE [ROLE_READALLEXCEPT1]

2) Create your "deny" role like so:

CREATE ROLE [ROLE_DENY]
GO
DENY SELECT, INSERT, UPDATE, DELETE ON myTable TO [ROLE_DENY]
GO

3) Then add your "except 1" role to it:

EXEC sp_addrolemember @rolename = 'ROLE_DENY', @membername = 'ROLE_READALLEXCEPT1'

4) Add your role to db_datareader:

EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'ROLE_READALLEXCEPT1'

The deny role should override db_datareader, and the net effect is that your role now has access to all tables (including new ones) except for those explicitly denied.

You can then add your users to "ROLE_READALLEXCEPT1" and they will have access to everything except the one exception table.