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