2

I have database that is a datawarehouse environment that loads data with an ETL process.

During the ETL process I wish to make the database unavailable for querying for certain roles.

What would be a possible solution?

Christian Phillips
  • 18,399
  • 8
  • 53
  • 82
Roland
  • 38
  • 5
  • Could you please provide code of what you have tried? – Hexie Aug 08 '13 at 07:33
  • 2
    @Hexie sometimes it's better think before you comment. it is not a select query or something like that , that he needs help with but a configuration modification – Royi Namir Aug 08 '13 at 07:35
  • Take a look here, may help you: http://stackoverflow.com/questions/4143558/sqlserver-lock-table-during-stored-procedure – Christian Phillips Aug 08 '13 at 07:36

2 Answers2

0

I think the easiest answer would be to REVOKE PERMISSIONS for the rolls in the ETL process and reverse it at the end (or on fail).

Gidil
  • 4,137
  • 2
  • 34
  • 50
  • Thanks for the reply. Revoke is an option. But what troubles me with revoke is that I have to specify the tables individualy. I would rather revoke all acces with a single command. – Roland Aug 08 '13 at 07:42
  • Assuming all the tables you want to "hide" are in one SCHEMA, you can just revoke permissions on the SCHEMA. Does that help? – Gidil Aug 08 '13 at 07:44
  • 1
    I think that's a great idea. I will try it. – Roland Aug 08 '13 at 07:47
  • Let me know if it works. If not I'll try to help you find something else. – Gidil Aug 08 '13 at 07:48
  • 1
    It works ;-). A deny on schema level overrules all grants on object level. So when I start the ETL I will deny SELECT on schema level and after the ETL finishes I will REVOKE that DENY. Thanks for the help. – Roland Aug 08 '13 at 08:26
0

One option would be to create a stored procedure which modifies the permissions of the roles, then drops users connections, then following the data load you reset permissions.

An alternative to this is to run your ETL process when no one is using the system...

Bravax
  • 10,453
  • 7
  • 40
  • 68