So, to warp up, we have a SQL level audit that fires a tigger to save the audited data. You will notice that I get the primary key, so using various tools you could set up the UX to notice that the unerlynig objects are audited and then return the audit trail for a specific object.
Anyway, the "install" file is located at the following location:
MediaFire File Download: CompleteSQLDataAudit_v1.0.sq
Do let me know your thoughts or if you have any questions with the source code. You should be able to open it in MS SQL Server Management Studio and run it in any 2008 database (assuming it works in other versions I don't have, see the first post about that). The audit control tables and stored procedures do not need to be in the same database as the tables to be audit. And you can audit tables in more than one database.
To start auditing a table you can just run the StartAuditFor stored procedure, and so long as the account has the access to the source table it will all work.
To re-compile the audit structure after updating a source table structure, execute the ReCompileAuditStructure stored procedure and it will check all currently audited tables.
To archive the old audit records, run the ArchiveAuditTables stored procedure. I would recommend this be done in a SQL Job, which I would run before the nightly backup for your SQL database (might as well clean up the data before you back it up).
No comments:
Post a Comment