asked
Audit database changes
The sox auditor want our team ( mostly accidental dbas) to provide on a monthly basisthe following information 1) All db schema changes 2) All users that have inserted, deleted and updated data. We are on sqlserver 2008 Can a report / query be run against our current environment that provides this information?
Unless you have some change tracking in action or C2 audit tracing enabled, you will not be able to query such information from system. If you have some auditing mechanism implemented, then you have to query your audit logs. But by default there is no such auditing enabled on SQL Server.
You can read up on SQL Server Audit (available from SQL Server 2008): [
http://msdn.microsoft.com/en-us/library/cc280386.aspx][1] Other ways of auditing is to have your application access everything via stored procedures, and have the stored procedures log changes and selects, and on top of that define DDL triggers to log DDL changes. [1]:
http://msdn.microsoft.com/en-us/library/cc280386.aspx
Yeah @Magnus Ahlkvist, but as I wrote unless they have some kind of auditing/change tracking in action, they will not be able to query such information. once the auditing is enabled, then it is and easy task. :-)
Yep, I'm pretty sure this time it will be a gap in the SOX audit report. On the other hand, a quickly closed gap looks almost as good as a clean sheet :)
In some cases, a quickly closed gap is better than a clean sheet. Auditors are just people and, if they can find some low hanging violation fruit that you are receptive to fix, they often don't dig much deeper. If everything looks clean, they often look a bit harder so they can find *something* (we all want to be good at our jobs, right?).
You should be able to get a good portion of that information from the default trace, that by default is enabled in SQL Server 2005 and beyond. You can check out this SQL Lunch [(#6 by Jack Corbett][1]) on querying it. It is pretty simple and can be done pretty quickly. I would discourage the use of C2 Audit, it captures everything that SQL Server does and on heavy load instances can cause some performance issues. And IMHO is just wasting drive space if you don't need all that information. If you want to get detailed you can simply create a server-side trace and get specific on what you need to capture for your reports. For future compliance you might want to invest in a software product that can capture all this information for you and build the reports. I don't know about RedGate though I'm sure they have one (@Grant Fritchey ???), but Idera makes a SQL Compliance Manager for just such a purpose. It is designed specifically with SQL Server in mind. Getting a product, if budget permits, can save you and your team alot of time. Time that you can put on support and improving your SQL Servers. Also, be sure to type up the options you are looking at and your plan of attack for getting the reports. This can be helpful in smoothing over the auditor, and show the effort you are taking. [1]:
http://www.sqllunch.com/Lunches.aspx?Month=December%202009#
Here's a [post I wrote last year][1] on DDL auditing which might be useful in this situation (you can expand audits beyond this as well). Key points are setting up auditing and how to get that data back into SQL for reporting purposes. [1]:
http://www.englishtosql.com/english-to-sql-blog/2010/2/8/ddl-auditing.html
