March 23, 2009 at 12:21 pm
Hi all,
I am also haveing a problem with monitoring all users ( including window logins and SQL Logins) in test, dev and production servers across the network. Also, i have to automate and notify me if there are users that are not need any more in the production system across the network?
Please let me know how can i achieve this? thanks a lot for your help in advance.
March 23, 2009 at 10:48 pm
What are you trying to monitor logins? logouts? failed logins?
As for tracking logins that are not used any more .. I did following:
1) Created new database DBManagement.
2) Created table called UserLogonDetails (ID, UserName, LastLogonTime)
3) Populate the table with all server logins right now.
4) I created a job that updates that table with a snapshot of all the logins that are logged in...
4a) It first updates the LastLogonTime for all existing logins.
4b) Then it adds any new logins that show up.
4c) Check table for any logins that have not logged in for 6months send me an email as reminder to check ...
This was on 2000; on 2005, you might be able to log it more actively using Notification Services, for example using http://www.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/ and then changing it like above...
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 24, 2009 at 7:18 am
hi Mohit:
Thanks for the response. I am trying to monitor following items:
1. Logins
2. Logouts
3. Loginfailed
4. Exixtence of users or ID in the server
5. Also, For example, we have two groups of DBA: Project DBA and Support DBA. I am a support DBA, and i have to grant access to project DBAs to production servers for one week and want to monitor if Project DBAs access is still in the production server or not.
I need a script to do this or something that helps me in achieving this task.
Thanks for your help.
Abi
March 24, 2009 at 10:43 pm
Abi Chapagai (3/24/2009)
hi Mohit:Thanks for the response. I am trying to monitor following items:
1. Logins
2. Logouts
3. Loginfailed
Possible using http://www.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/ article (very good article)
4. Exixtence of users or ID in the server
Possible using the article again, you would modify an existing table for every successful login ..
So if you take my example ...
1) Created new database DBManagement.
2) Created table called UserLogonDetails (ID, UserName, LastLogonTime)
3) Populate the table with all server logins right now with lastlogontime set to null
4) In the processing que store proc you will update this table every time there was a successful logon for last logon time.
5) Create a job to send you an email when there are Logins that have not been used for a while.
5. Also, For example, we have two groups of DBA: Project DBA and Support DBA. I am a support DBA, and i have to grant access to project DBAs to production servers for one week and want to monitor if Project DBAs access is still in the production server or not.
I need a script to do this or something that helps me in achieving this task.
Thanks for your help.
Abi
For this I would set up another table lets call it TempSysAdminAccess with two columns Username and ExpiryDate.
Every time you grant someone from Project team access you add a row in this table with
JoeSmith,4/10/2009
And create a job that checks the TempSysAdminAccess table and does one of two actions a) automatically remove the permissions for you b) email you to let you know permission must be removed.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply