Auditing SQL 2005

Question

HI,

We have a requirement to audit access to production data. mean audititing any activity on sensitive production data like Select/DML/DDL etc on specific database.

We have evaluated options like

Enabling Successfull/failed login - which will not capture what activity the user is doing on the box

C2 Audit - which is too much

SQL profiler in the backgroud: this seems to be the most viable option where a trace will be started when the server starts and the trace files will be saved to a safe file share.

if we reduce the number of events to be captured, will it reduce the load compared to a profiler running default options?

Answer 1

Yes, the less events you capture, the less overhead. Also running it on a different machine and pointing it to production server will help. You need to be careful though. If the database is busy, the files will get very big, very quickly...

---

Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Answer 2

thanks Peter for your quick response.

Is there a differnt way of auditing SQL 2005 instance databases, other than the three options mentioned above?

Answer 3

Well, you could use triggers for Insert/Update/Delete, but they can't fire in response to Select, so if you can live with the overhead, profiler (or SQL Trace) is probably your best bet. Profiler is just a GUI for SQL Trace, so SQL Trace cuts out this overhead, but you get the same effect from running it on a different server as I described above.

---

Peter Carter-Greenan http://sqlserverdownanddirty.blogspot.com/

Answer 4

This sounds like a good reason to upgrade to SQL Server 2008 Enterprise. No, I'm not in sales. I just wanted to make sure you were aware of the SQL Server Audit feature. If you are interested, see Understanding SQL Server Audit http://msdn.microsoft.com/en-us/library/cc280386.aspx 

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty