Post reply

Auditing Logins in Sql Server 2005

  • August 17, 2009 at 8:29 am

    #213676

    Hello All,

    I am using the below code to caputre logins it works great.

    http://www.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/

    I wanted a piece of code in the stored proc to acheive the below...

    If a particuare username already exists in the "Logging" table in a day.

    I dont want to capture/insert it again.

    This way i am trying to eliminate duplicate captures and maintain the size of the table or if there is a better way to do this.Please let me know.

    Any suggestions would be greatly appreciated.

    Thanks in Advance!

  • August 17, 2009 at 9:43 am

    #1040288

    you could easily check to see if the person had already logged in on that day by checking user name, day, month, year before inserting.

    However, are you trying to just see who logged in during a particular day? If so, how does that help you with auditing?

  • August 17, 2009 at 9:50 am

    #1040296

    Steve,

    Thanks For Your Reply!

    We are primarily using this to remove unused id's on a Server .If a login has not been used for 90 days we want to look at it and talk to application team and remove if not necessary any more.

    I wanted to check before inserting...but i couldnt figure it out....

    Thanks Again...

  • August 17, 2009 at 10:20 am

    #1040337

    Steve nailed it.

    However, I'm not sure if this would be applicable in your environment, but we had one user who used their login credentials in an excel spreadsheet and this was still active after he left.

    We're only a small company, so I know who is here and who has since left...For a company with hundreds or thousands of users, you might well cross reference your login list against an HR employee list to see if you have any applications using logins which need removing.

  • August 17, 2009 at 10:28 am

    #1040347

    The check should work

    if exists (select from ... day = day, month = month, etc.)

    Then I'd disable those not used for 90 days, but I wouldn't remove something until 365 days had passed. I've been burned by those once a year processes before.

  • August 17, 2009 at 10:32 am

    #1040349

    Thanks Steve!

    Thanks For The Tip...

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

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