Earlier this year, I posted a short feature on auditing in SQL 2005 using DDL triggers to capture events and log them. In SQL 2008, one of the less sexy features added, though highly important and underscored by this current eras reporting and traceability debacles in public documents, is SQL 2008’s new auditing features (less sexy from a populist point of view, I do note that there is probably an avid community out there with an audit fetish, so no intention of diminishing this feature’s importance to you and yours).
Quite simply, it is implemented by the following sample syntax (full reference here):
One of the interesting switches you can use for auditing is the ON_FAILURE argument accepts parameters of CONTINUE or SHUTDOWN. If auditing is paramount for this system, then any failure in the auditing mechanism will turn the SQL Server service off. As auditing is SQL instance specific, this can be useful for many types of compliance scenarios.
The audit is set up at two levels; first, as shown in the script above, at the server level, which captures audit events (more on those events here).
The second level that can be implemented is at the database level. The script below is run within the context of the database I wanted the audit on, while the first script needs to be run within the context of the master database (full reference here):
This particular audit specification will capture all DDL executed against my particular database and make it part of the log.
For more, you can reference books line here for the Auditing home page for SQL 2008.