Sql-server – Auditing specific database events

auditsql serversql-server-2008-r2

For auditing purposes, we have a requirement in which we have to enable Auditing for theses events:

  • DBA Logins
  • Changes made by DBAs LIKE INSERT, DELETE, UPDATE, ALTER (etc.)

Changes made by our application are stored in tables (CreatedBy, ModifiedBy, CreatedOn, ModifiedOn) and row changes are stored in XML in a specific table so we won't need to log changes made by our application.

Previously we had C2 Audit Mode temporarily enabled, but due to data volume and performance considerations and having these requirements in mind we considered it excessive and disabled it.

Enabling C2 Audit mode is fairly easy, how can i configure the database to perform this kind of logging ?

Additional Notes:

  • Currently our server uses SQL Server 2008 R2 Enterprise but since we have mostly data storage and reporting services we'll be downgrading to SQL Server 2008 R2 Standard .

Best Answer

SQL Server 2008 (r1) introduced Extended Events and with that came SQL Server Audit and works similar to XEvents to audit events. So it is less intrusive than C2 Auditing and server side traces.

It is very granular and fairly easy to setup. This feature is available in all editions of SQL Server 2008 R2. A good write-up intro/how-to can be found here by Brad McGehee. Thomas LaRock also did a great article on Simple-Talk walking through creating/setting up the audit.

Reading through the articles mentioned above you will note that you can have the events logged to different places (windows app log, file, etc.). From that you can then write PowerShell scripts or T-SQL scripts to alert you of whatever you want. Much more easy than screwing with C2 Audit trace files.

Related Question