Audit Prep Toolkit


October 17, 2011 by Mike Hillwig

When I was the DBA at Acme Packet, we went through a Sarbanes Oxley audit at least twice a year. It’s the price you pay for being a publicly traded company. One of the things I learned in my tenure there was that the best way to survive an audit is to anticipate what the auditor is going to ask for. Over a few years, I developed a great rapport with my auditors and typically had a mountain of data for them to sift through before they even walked through the door. By putting together a handful of scripts and reports, our auditors were able to spend more time doing actual auditing instead of waiting for us to provide data.

I’m working on a toolkit that DBAs will be able to use to have this data ready for their auditors. It’s just a handful of scripts that generate the data needed to demonstrate some basic audit controls. By dumping that data into the BI engine of your choice, it will look like you know what you’re doing and are well prepared. Here are a few things that you can expect.

  • Basic Server Configuration Info
  • Database logins
  • Database logins with the sysadmin role
  • List of users per database
  • List of users per database including role
  • List of database roles and users included
  • List of explicit grants for database users
  • Backup history
  • Failed backups
  • Failed backups and proof of notification
  • List of SQL Agent Jobs
  • SQL Agent Jobs and Schedule
  • SQL Agent Job History
Some of these sound redundant, and they absolutely are. It all depends on what your auditor cares about during that particular audit. And frankly, I’m okay with the redundancy because it keeps an auditor off my back.
  • It was a very interesting post thanks for writing it!

  • Felicity

    Thanks for sharing your list.
    I don’t think any of these are redundant. I participated in my first audit this year (this was never a requirement until my current employer), and fortunately I had the findings from last year as a guide as to what information was expected of me. I was forced to come up with reports and also fix anything that may result in findings before the audit.
    When the auditors came out there were some scripts that I ran that were similar to each other, with just a few additional or less columns. They were really pleased that I had most of the stuff ready for them as they sometimes spent 4-5 times longer with the other teams getting their information.

  • Heya we are with the most important time here. I stumbled upon this particular board and I in finding It genuinely beneficial & them helped me to out and about very much. I really hope offer one thing again and aid some others just like you served myself.