Post reply

How to monitor SQL account usage?

  • February 17, 2004 at 7:40 am

    #178212

    Does anyone know of a tool that can be used to tell you when the last time a standard sql account was used.

    The purpose of this would be to get rid of old stale accounts that are no longer needed.

    Thank you.

    Alexzander N. Nepomnjashiy

    Technical editor for Wrox Press:
    "SQL Server 2000 Fast Answers"

    Technical editor for Apress:
    "Honeypot for Windows"
    "SQL Server Yukon Revealed"

  • February 17, 2004 at 8:39 am

    #494377

    I'm sure there is a tool you could purchase to monitor this, but I don't know what that would be.

    I run sp_who2 every 10 minutes on my server and put the output into a table.  I then insert that data along with the run_time into a table where I maintain a history.  Then I query for IDs over time and see if they are still being used or not.  Yes, many times a connection won't last over 10 minutes, but I figure the odds will cause it to appear and I don't want to put too much traffic on my system for monitoring.



    Michelle

  • February 19, 2004 at 9:06 am

    #494729

    SQL Server Profiler.

  • February 20, 2004 at 10:10 am

    #494970

    If you find a few, disable them for 6 months or so (deny access) before you delete them. Can save you some headaches.

    Course, I'd be tempted to just disable any I suspected and see what happens.

     

  • February 26, 2004 at 9:02 pm

    #496085

    You can turn "Both" Audit Option from Enterprise Manager SQL Server properties. This will write login success and failures to SQL Server errorlog. You can scan the errorlog at frequent intervals and store the login failure and success dates in a table. Based on these dates you can take the required action.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply