4

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 .
4

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.

3

I know that ApexSQL Log can audit changes made by DBAs LIKE INSERT, DELETE, UPDATE, ALTER (etc.) but CANNOT audit DBA Logins.

However, they are announcing a new tool that can track "who saw what" and attempted logins, so there might be something there: Sneak Peek: ApexSQL Comply - Part II, Introducing ApexSQL Comply

It seems that it can also track inserts, deletes and updates (see the shot and they mention a complete audit trail). But there's no info when this tool will be available, nor they mention a beta version, which would be nice to have for testing, as this looks like a very useful tool.

0

To know the details about the successful and failed logins in the SQL Server, I can recommend you to try in a Lepide's tool which has the ability to examine each and every activities regarding the who, what, when and where for each logins done in the SQL Server. The tool has the speciality in generating the report telling the details of who, what, when and where for the create, alter and drop operations performed on the database and SQL Server objects also.

I am providing you the link of the tool http://www.lepide.com/sql-server-audit/ and this tool do supports the capture of DML activities.

  • The OP is wanting to capture DML activity. I read your answer to say this tool only captures DDL activity? – Shawn Melton May 17 '13 at 20:48
  • Using build in software is preferable since it simplifies the process and we don't have extra costs. – Carlos Ferreira May 20 '13 at 12:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.