Determine date when each user last accessed the database

I need to clean up some security issues. We have numerous users who access the database and I'm not confident which logins can be deleted. I've already done sp_who and sp_who2 and neither of them gave me exactly what I wanted (I know they didn't an employee who was terminated but certainly access the database numerous times before he left). I also looked at the sys.sysprocesses table. Is there another system table that can be tapped into for this?

Thanks!

It depends on whether you're looking for a change to the objects or simply an access to the data. For data, you need to set some things up to track who has looked at it recently - and that will only work if they used an application that reports who the user was, such as SQL Server Management Studio. If you have not set the system up to track data access or the application they used to get to the data doesn't report the accurate name of the user, then you won't be able to go back in time to find out what they did.

Thanks -

Buck Woody

|||

One option would be to create a trigger to populate a table of user logins. This might be resource intensive if there is a lot of login activity.

Or, you can turn on login auditing for successful logins and search through the Event log.

You can use a third-party database auditing tool.