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.

Designing reusable column transformations?

We receive thousands of files every week from various clients and we attempt to clean the columns using the same technique over and over so the data is consistent. The problem is I dont see a way to reuse complex column transformations in different packages. I would hate to have to go change every package if we change the rules for cleaning a column.

So #1: Can you create some kind of script or .net function that cleans a column and reuse it in multiple packages (or even in the same package)?

#2: Is it possible to call functions from the Derived Column expression builder?

Thanks!

1)May be. You always can use a script component with the transformation rules and copy and paste it many times; it that sounds reasonable to you.

2) No you can not.

|||

Craig, I have had do do the same thign.The best solution is not a script component, as the person above mentioned.

Take the hit, build a custom component. Once you get it built, adding a new column is as simple as adding a new column to the transform.

You will want to build a custom pipline component. Both Wrox and O'riely have some decent chapters decribing how to do this. and there is definately info in microsoft.

I have 350 packages, that all chagne time from european time to us time, in the data flow. By writing 1 component, once, I was able to then delegate the rest of the work, since the hard part was encapsulated in a compoent.

good luck