1

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

Ronald
  • 13
  • 1
  • 7

2 Answers2

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.

beercohol
  • 2,577
  • 13
  • 26
  • Stupid question maybe, but in which role should i put the user that must have acces to all tables. And in whitch ROLE should i put the user that has acces to all tables except of the exception table? – Ronald Aug 04 '15 at 14:12
  • I've added to my answer to make it clear that you should add your normal users (who need access to everything except the one table) to "ROLE_READALLTABLES". If you need another user with access to absolutely everything, you could do any of: 1. create another role (probably best), 2. add them direct to db_datareader, or 3. make them dbo, if appropriate. – beercohol Aug 04 '15 at 14:22
  • I've updated the role names in my example to try and make it clearer still. Hope that helps! – beercohol Aug 04 '15 at 14:36
  • Thank you very much, Tomorow when i'm back at work i will try it out. – Ronald Aug 04 '15 at 15:14
  • And thanx for your updated table examples. There are crystal clear. I do have one question. Because of the fact that the tables are recreated every day,what should i do about the ROLE_DENY. Should i make a job with the command: 'Deny Select, Insert, update, delete on mytable to ROLE_DENY' that runs every day. Because if i don't the DENY permission on MYTable wil every day be gone. – Ronald Aug 04 '15 at 15:31
  • What, you mean that your one exception table will be regenerated with a different name every day too!? My answer is based on the assumption that this table will not change every day. Please clarify! – beercohol Aug 04 '15 at 15:40
0

there is no information about the excluded table so i assume is always the same.
I also assume that all the other tables are on the schema dbo; this is not a relevant constraint or limitation because the logic can be easily applied to more than one schema.

the easiest solution is granting permission at the schema level.
move the single table on a separate schema with restricted permissions and grant full read on the whole schema where the user tables reside:

GRANT SELECT ON SCHEMA::dbo TO [relevant role/user];

now the developers can create all the table they feel like on the schema dbo and the permission are inherited by the schema.
should you need to grant access to more than one schema the permission are easily applied once and then every new table will get proper permission.

the huge pro of this solution is that it is fire and forget: once in place there is no maintenance, no jobs, no script to run daily/weekly/whatever.

this advantage is to be evaluated and weighted against the move of the excluded table (or the other way round: move the user tables): maybe is used by just a couple of internal applications so it is a quick patch or is used by a whole bunch of services accessible worldwide instead and that would be a nightmare.

Paolo
  • 2,224
  • 1
  • 15
  • 19
  • There is one exception. This table has schema [dbo]. like all other tables – Ronald Aug 04 '15 at 13:41
  • that's one of the assumption i made even if i didn't write it out and that's why the first operation i suggest is to move that table to a different schema. unfortunately there are no info about that in the question so i had to make lots of guesses. that table can be moved? – Paolo Aug 04 '15 at 13:47
  • unfortunately that is something that can not answer because i myself am not responsible for changing tables structures. I am not allowed to do this, – Ronald Aug 04 '15 at 14:17
  • But offcourse i will take your advise with me and will look what the possibilities are in our database. Thanks – Ronald Aug 04 '15 at 15:11
  • there's another player that just came to my mind: synonyms. move the excluded table to a separate schema and create a synonym on `dbo` to keep the old path 'alive': this way you can go with schema level permissions maintaining compatibility with existing applications. – Paolo Aug 04 '15 at 15:27