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 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]) 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. :