How to restrict access to SQL Server user in some period of time

Question

I have two SQL Server users 'A' and 'B'.

I use user 'A' for ETL and 'B' for reporting.

My requirements is to restrict user 'B' access while user 'A' performing ETL (Extraction, Transforming and loading)

Answer 1

You can :

Method1

1) Create a trigger, in case if its the concern for a single table that user A should be not be conflicting. Therefore you can create the trigger which should restrict user A from the current permission it have , that you would like to restrict.

2) Once done with creating trigger, you will need to create an SQLAgent job which enables the trigger during the time when user B starts its process and second step of that job will be to disable the trigger when user B finishes it tasks.

Method 2

You can also, in case the whole database is in question where user B needs no conflict with user A you can script out the permission for user A and schedule a jobs which (during you're required time frame)will remove the permissions of user A and second job which add backs the permission of user A after a defined time as per the need.

There can be various other methods for sure, but may be i am not able to recall as of now. Hope if above 2 helps.