December 9, 2011 at 7:39 am
I have an end user that I would like to only allow access to a database for part of every month. For example, I'd like to block their access until the 10th of each month, then allow it for 3 days, so the 10th, 11th, and 12th they can login, but all other days of the month they're blocked.
I thought about doing this through a scheduled job perhaps if I can enable/disable their login ID, but I'm not sure if that'd work for sure, or if there is a better way to do this.
Any thoughts, suggestions, help is appreciated.
December 9, 2011 at 9:01 am
Interesting scenario;
do you really want to prevent access completely, or should they only have read only access during specific restricted periods?
if it was read only access, you could use a scheduled job to either set the database to read_only, or change the role the user belongs to to a group that is only read only and back again after the period is over;
you could disable their logins, like you thought as well;
A last resort posibility could be to use a logon trigger i think, which I'm mentioning for completeness, but is really a poor substitute for the ideas above.
Lowell
December 9, 2011 at 11:49 am
Yes, I want to completely disable their access except for certain days of the month. This will prevent them from accessing the db and running large queries against it while it is in the process of updating large tables with significant amounts of data.
I think I will just use a scheduled job to alter the login between 'enabled' and 'disabled' conditions. I was just wondering if there was a better way to do it. Thanks!
December 9, 2011 at 1:36 pm
Personally I like the scheduled job idea.. Toggle their access on and off automagically..
CEWII
December 9, 2011 at 2:24 pm
I have a comparable scenario adn that's exactly what I'm doing. Job enables the login when I want them to have access, disables it otherwise. You may want to run a few checks/reports/alerts to make sure it happens as desired.
December 9, 2011 at 2:33 pm
Pam do you youse a calendar table for the on off entries, or just a schedule assumption (ie biz hours mon-fri);
i tested the concept with my calendar table, no problem moving my user into restricted roles or disabling their logins, but if i use a calendar table, i have to update that for as many months/yeasrs int eh future i want to maintain, vs a set assumption.
Lowell
December 12, 2011 at 8:58 am
Lowell, what I do is pretty basic - on Tuesday start of day, off Thursday end of day (or thereabouts). Sometimes I'll turn users off towards the end of the month through the start of the month but that's rare. (For systems that require that much I prefer to create an isolated reporting DB of some flavor.) No changes for holidays, etc. I don't work much with calendar tables for precisely the reason you specified. The few times I have attempted this I've wound up with more headaches that it's worth for the business needs I've had. One of these days I'll dig into them but I've got plenty enough on my plate these days.
December 12, 2011 at 9:11 am
I agree that scheduled job is the way I would go...I guess you could also use a logon trigger if you were looknig for an alternative approach.
December 12, 2011 at 9:46 am
Job is the way to do this. You're scheduling something.
However I'd make it fairly generic. Run it every day, enable/disable people based on a day of the month. Building and populating a calendar table to run for a decade is easy. Add another table that lists users, and populate it with dates to enable/disable. Populating that for the last 3 days for a user ought to be fairly simple as well.
One last note, if possible, use Windows Auth IDs, not SQL ones. That way if someone leaves, the AD account gets whacked and it doesn't matter what you do in SQL. If you need SQL logins, then you need to add a note to your separation process that removes this.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply