Auditing in SQL 2005

[ousoonerjoe]

Looking for suggestions on setting up Auditing on an SQL 2005 database. There's several different ways to get there and many different theories on what is the 'best' way to do it. We are testing the 'C2 Mode' that comes with SQL 2005, but also looking at adding CreatedBy, CreatDate, ModifiedBy, and ModifyDate to each table and having Triggers populate the fields.

'C2' has a low overhead, but fills a 200 MB file pretty fast. To top it off, it wants to place the files in the install path of SQL Server and covers ALL databases including the Report Services. There are give and takes to all methods. Just curious as to what other ideas are implemented out there and what you think is the best compromise.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[markros]

See

http://articles.techrepublic.com.com/5100-10878_11-6175865.html

 

[ousoonerjoe]

My problem with that one is Triggers. We have triggers in place already that will UPDATE '1/1/1900' to NULL. I'm already fighting this with the ModifiedBy, createdby, etc plan. The triggers will fire which will cause extra entries.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[RiverGuy]

The triggers will fire which will cause extra entries.

That's where sp_settriggerorder can come in handy:

 

[markros]

I was looking for something else and came across this interesting article

http://www.mssqltips.com/tip.asp?tip=1381

 

[ousoonerjoe]

After running the 'C2' trace logs for a while, we've found that it encompasses a LOT more than what is really needed. It includes everything that happens on that server and every database. While we could work with it, it has one down fall, any values passed through are saved as arguments. We need to be able to see the actual values being passed back and forth.

Is there a way to make it so the arguments are displayed as actual values or will we need to just create our own audit tables and update all the calls to make entries to those tables?

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------