Hmmm, I'm thinking more Service Broker. Or maybe even a streaming database like Streambase. But I suppose you could get SSIS to do it. Maybe an SSIS package called via service broker. Any thoughts there?

You can store all events for later analysis but for a real-time application you want solid rules-based alerting. "Let me know when something isn't right. Otherwise, don't bother me."

Technicalities aside, the age-old question comes up: Who audits the auditor? At some point you just have to trust that your DBA isn't out to screw you over.