Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auditing Logins in Sql Server 2005 Expand / Collapse
Author
Message
Posted Monday, August 17, 2009 8:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:37 PM
Points: 127, Visits: 553
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
Posted Monday, August 17, 2009 9:43 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Posted Monday, August 17, 2009 9:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:37 PM
Points: 127, Visits: 553
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
Posted Monday, August 17, 2009 10:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:41 AM
Points: 1,159, Visits: 6,478
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
Posted Monday, August 17, 2009 10:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: 2 days ago @ 3:11 PM
Points: 31,368, Visits: 15,837
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
Posted Monday, August 17, 2009 10:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:37 PM
Points: 127, Visits: 553
Thanks Steve!

Thanks For The Tip...




Post #772089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse