Is there DDL Auditing in SQL Server 2000

Question

And if not, is there a way to tell when a trigger was disabled/enabled?

FOLLOWUP:

It's a rather interesting diagnostic case. I was only involved from the periphery, and the guy doing the diagnostics isn't a database guy.

Anyways, he had a trigger that would move data from one table to another. He did a comparison and not all the data had made it to the second table. I said, I'm a critic of SQL Server but I trust that their triggers fire in the same transaction. He said but some of the data made it... if it was just disabled, nothing should make it. True. So I said maybe someone is enabling and disabling the triggers. Hence the question.

But what really happened is someone permanently disabled the trigger and copied the code into a sproc that was set to run at a certain time.

The correct forensic test would have been to look at the dependencies of the second table, see what else was using it. That would show the tumor sproc... (I've been watching lots of House reruns, can ya tell).

Answer 1

No auditing, though there is a company called Lumigent that offers a product "Audit DB" which will do DDL auditing (among other things) for SQL Server.

You can look in the sysobjects table for the crdate which will tell you when the object was created.

Answer 2

Your problem looks quite similar to the one that Randy Volters wrote about in Simple-Talk

http://www.simple-talk.com/sql/database-administration/dml-trigger-status-alerts/

I suspect it will help