July 2, 2008 at 10:13 am
Hi Techies
I would like to get some suggetions to implement a weird request for disabling (locking ) or drop SQL Logins that are not used in 90 days .
My Environment is Windows 2003 Enterprise Edition
SQL Server 2000 SP4
I know in SQL Server 2005 it is inbuild but i have 2000 in my environment.
How can i keep track the login information in SQL Server 2000 as only place i found is sysprocesses but that is also not a good option too.
Please suggest to implement this task.
Thanks in Advance for your time and advices
July 2, 2008 at 2:27 pm
yep a weird request and there is going to be an overhead in doing it. I would confirm the reasoning behind this request. Point out this is not inbuilt into SQL when using SQL logins so perhaps they are approaching it from the wrong direction.
Off the top of my head:
Add something to the app to force password changes after 90 days, or add a table to the user db which logs when they log onto the app, them something can be written to alert you if not used for 90 days.
Even better switch to windows authenticated ids and let the OS handle it. This would be best way to do this.
Put proper admin processes in place so when someone leaves the company there id is removed from SQL and whatever else they use.
Upgrade to SQL 2005!
Otherwise you will need to run profiler full time to audit logons and output this to a table. This can then be read and used to update another table which lists all the ids and update it with the login date, scan this table for any ids with a last logon > 90 days ago and drop\change password of those ids.
some designer out there might have better ideas but thats my two pence worth
---------------------------------------------------------------------
July 2, 2008 at 3:34 pm
Thanks George
It's not possible to upgrade to 2005 as company want to wait and then plan to upgrade to 2008 and also most applications works on SQL Server Authentication and it's also not feasible to change to Windows Auth.
Running Server Trace is the only other i am seeing viable but again lot of head ache to keep track the trace information in table and keep updating ..
July 2, 2008 at 3:52 pm
agreed its a pain, espec if you are talking about multiple apps. but it's doable and Cannot see any other way. Will be a performance hit as well so admin processes should be investigated.
perhaps theres a 3rd party tool out there?
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply