Need to audit when a login updates our database.

[bessebo]

We are undergoing an audit and I am looking for a 3rd party software package that will track any changes made to our ERP database by the "sa" account (or any chosen logon for that matter). We are running SQL2000 and we do not want to turn on c2 level auditing because that will track all changes made to our databases by any user and could severely bog down our production environment. Our ERP vendor does have this type of product but it does not work with our older version of their software.

I would think that there must be a 3rd party software that does this. Any help would be appreciated.

Regards,
Bessebo

 

[mrdenny]

You can setup a trace to run automatically which will trace what the sysadmin does (or any user that you configure the trace to monitor).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bessebo]

So you are saying to have a trace run continuously throughout the day?

 

[mrdenny]

Yep. You can schedule it to start each time the SQL Agent starts by getting the settings for the trace into a T/SQL script and scheduling that to start each time the SQL Server Agent is restarted.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bessebo]

Wouldn't that be a load on the system? After reading a bit about c2 auditing there is a great bit of concern about the load on the system and degrading performance. Do you have any experience with c2 auditing (advanced option flag). I think it is true that once you turn that on you are actually tracing all changes to the database. Any knowledge you have on c2 auditing?

Thanks,
Bessebo

 

[mrdenny]

Correct C2 auditing traces every command on the system. If you have a high load system then this can place a lot of stress on the disk where the trace file is written. I'm not talking about tracing everything, just specific users who have sysadmin rights.

DBAs shouldn't be in the system much unless they are working on something so they shouldn't be doing much, so there shouldn't be much to trace.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog