SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SOX in the City - Preparations for Audit

As I was going through some of my documentation at past clients, I found my summary of the general SOX compliance considerations in preparing for an internal audit for Database Servers.  I thought maybe it could be helpful to some folks who need to whip their servers into shape, and adhere to best recommended practices that would put you in compliance, whether or not SOX rules apply to your company.  While the below checklist does not get into some of the technical methods to facilitate in auditing your database environment, it puts forth the considerations that us as DBA's need to consider and think about if asked to provide assistance in audit & compliance.  Perhaps in future blogs, I will present some scripts and links that will supplement the below checklist.

Summary: The criteria in this summary are meant to serve as a general guideline to follow in drafting our database compliance policy.  We must aim to adhere to the general principles of SOX compliance (as broadly defined), but more importantly follow precedence of other public entities.  Specifically, we define our policies, document them, adhere to them, and enforce them.  This entire process is in effect self-contained, - “say what you do and do what you say.”  Auditors look for this consistency in ensuring our compliance and applying these rules.


For example, document what the access rights are and who needs them and then have a trail of it by having a record of someone being granted access, whether via email, a log, (electronic or print ), or some auditing system/database that notes when you grant access.

  • Control access to the database environment. 
    • Only give people the bare minimum rights necessary.
    • Have access authorized (you may need an approved list of authorizers), and keep copies of the authorization.  No developer access to production without proper approval, documentation and expiry.
    • Review access regularly, and have it re-authorized.
    • Treat generic or service accounts like people, and don't give them too much access.  Control who knows their credentials, and keep a catalogue of these accounts and functions. 
  • Segregation of Duties
    • This is a tough one, but make sure that no one has too much access such that they can modify the production system without having to ask anyone for permission. 
    • Try to keep control over the production and test environments separate.  This forces people who prepare a change or new release to seek approval, and show their homework is done before they can release their change.
    • There will always be someone with full control over a system, so make sure their actions are monitored
    • Review segregation of duties regularly.  Consider it for every process or rights assignment you have. 
  • Change Management
    • Don't let anyone authorize and/or implement changes they initiate.
    • Allow the DBA the right to refuse any implementation that does not have proper documented authorization.
    • Do not make record level changes to application data unless fully authorized, justified and documented.  These kinds of changes often help users out of a jam, but they go completely around the application controls.  Denying users this ability might lead to better business process in the end, and get you off the hook for problems that might crop up from such changes.
    • Document the day-to-day configuration changes you make.
  • Monitoring
    • You may be able to trade off some segregation of duties concerns if you are able to provide strong monitoring of actions.
    • DBA activity should also be included in any monitoring process, and documented.
    • Audit Schema/DDL changes to datatabase objects, structures, and login/user creation.
    • Follow-up all issues discovered in the monitoring process.  Regular and/or ad-hoc reporting, and alerts.
  • Backup/Recovery
    • Have processes written for both.  Test them regularly.  Document the testing.
    • Review backup logs, and document the reviews to show that you're looking at them.  You don't want to be caught saying I check them every day, and they're fine.  If the auditor finds an unreported error, their trust in what you say will take a hit.
    • Review backup schedules, and make sure they represent current agreements with your business units.
  • SQL Code
    • Make sure access to SQL code (triggers, stored procedures, anything additional used in DTS/SSIS packages) is controlled.  Do not make the code or schemas accessible to anyone who does not have a legitimate need.


Want quality Remote DBA Services?  Want enterprise sql server monitoring?

Try SQLCentric:http://www.pearlknows.com


Posted by Anonymous on 23 August 2009

Pingback from  Bookmarks for August 23rd | Brent Ozar - SQL Server DBA

Posted by Anonymous on 23 August 2009

Pingback from  Valuable Internet Information » SQL Server Central

Posted by Hugo Shebbeare on 24 August 2009

Thank you Master Pearl for the great checklist, not many step out to elaborate you have.

I've posted on this subject early on this year also: www.sqlservercentral.com/.../the-importance-of-the-segregation-of-duties-with-respect-to-internal-controls.aspx

I often refer to the segregation of duties as also the required physical divide between the developers and production database administrators. The latter being the required 'fresh eyes' hoop before volatile code shows up in production.

Posted by Anonymous on 24 August 2009

Pingback from  The Importance of the Segregation of Duties with Respect to Internal Controls - The Database Hive for SQL Server DBAs

Leave a Comment

Please register or log in to leave a comment.