How to close and open access to SQL Server 2008 in Windows application?

Question

Hi,

I'm using SQL Server 2008 (without application server or anything).

Numbers of users can be up to 1000. Windows Authentication is used.

The question is:

How to handle modes, so

1. some users will be allowed to work in read-only mode

2. some users won't have access to db for some time

My versions:

1. Using a table with a mode id for every group of users, that will work the same way. On Form Load application will query that table for mode id.

2. Using trigger on the tables, that must work according to that mode. The trigger will query mode value and doesn't work if access is closed or it's in read-only mode

I know these are not the best solutions, that's why I'm asking for your advice.

There's one more point.

If the mode is changed to "access-is-closed" for a group of users, that group must not be able to query to DB starting that moment.

With first solution I wrote it won't work, because user can be in application at that moment and no form load event will work. How can I do this?

Is there any optimal solution?

Thank you. Any help would be appreciated.

Answer 1

It depends somewhat on how you Access app interacts with the server, but for number 1 why not just use the built in Role/User permissions system in SQL Server?

For number 2, as your using Windows Authentication you can restrict logon hours in active directory on a per-account/OU basis this should prevent them from logging on to SQL server.

You could also do it via Logon Triggers which would not also prevent access to other domain resources.