Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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 .
share|improve this question

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
Spot on! Exactly what i wanted, thanks ! It's also more simple than i thought :) – Calbertoferreira May 15 at 14:24
1  
If moving from Enterprise to Standard then check on the differences in SQL Server Audit between the editions; there are EE only features. – Jason Cumberland May 20 at 13:34
Seems SQL 2012 limits Database level auditing to Enterprise only: msdn.microsoft.com/en-us/library/cc280386%28v=sql.110%29.aspx Going by the features supported page for SQL 2008 R2, SQL Server Audit is not even available in Standard: msdn.microsoft.com/en-us/library/cc645993%28v=sql.105%29.aspx – Shawn Melton May 20 at 17:41
up vote 3 down vote

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.

share|improve this answer
Check Shawn's answer, after looking with more detail in technet you can audit login and logout events successful or failed. Check here technet.microsoft.com/en-us/library/cc280663(SQL.100).aspx – Calbertoferreira May 15 at 14:27
up vote 0 down vote

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.

share|improve this answer
1  
Please provide a link to Lepide's tool. – StanleyJohns May 17 at 20:17
The OP is wanting to capture DML activity. I read your answer to say this tool only captures DDL activity? – Shawn Melton May 17 at 20:48
Using build in software is preferable since it simplifies the process and we don't have extra costs. – Calbertoferreira May 20 at 12:54
Please don't post multiple answers to the same question if they aren't DIFFERENT answers. You can edit your existing post to put the new content in. I've already done that for you this time. – JNK♦ May 22 at 12:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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