Isn’t life grand when you have a standard approach? That is typically my frame of mind, which is one reason why I advocate for a standard approach to designing, deploying and managing your SQL Server 2008 audits. I have developed the attached standardized scripts which should get you started on your standardized approach.
The script SetupAudit_Shell is a single SQLCMD script that will create the Audit, the Server Audit Specification, and a Database Audit Specification in every user database (except Model and TempDB). The script is written to send the audit data to a file destination. You will need to alter this part of the statement if you prefer to send your audit data to the Windows Security or Application log. The Server Audit Specification and the Database Audit Specification statements do not specify the audit action groups – you will need to supply these based on what you want to audit.
The script SetupDataAccessAudit_Shell is a script to alter an existing Database Audit Specification with table level audit actions. This will apply the exceptions to your standard audit when compliance goals require you audit DML activity from tables which store sensitive data.
The remaining scripts (DisableAudit_Shell, EnableAudit_Shell, RemoveAudit_Shell) are scripts to help manage the audits which are deployed with the defined standards.