Auditing Logins in Sql Server 2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » Auditing Logins in Sql Server 2005

Auditing Logins in Sql Server 2005

Rate Topic

Display Mode

Topic Options

Author

Message

chinn

Posted Monday, August 17, 2009 8:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:32 AM
Points: 127, Visits: 490

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!

Post #771948

Steve Jones - SSC Editor

Posted Monday, August 17, 2009 9:43 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

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?

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #772022

chinn

Posted Monday, August 17, 2009 9:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:32 AM
Points: 127, Visits: 490

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...

Post #772031

Clive Strong

Posted Monday, August 17, 2009 10:20 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, May 13, 2013 10:12 AM
Points: 959, Visits: 6,341

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.

Post #772075

Steve Jones - SSC Editor

Posted Monday, August 17, 2009 10:28 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #772086

chinn

Posted Monday, August 17, 2009 10:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:32 AM
Points: 127, Visits: 490

Thanks Steve!

Thanks For The Tip...

Post #772089

« Prev Topic | Next Topic »

Permissions