Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Working Your SOX Off

By Brandie Tarvin,


It used to be a mainframe joke. "What is a SOC4?" people would ask when the error came up. The answer: "To put on your foot, of course". Now, however, SOX is a word that strikes terror in the hearts of IT people everywhere.

In 2002, the United States government passed a law called the Sarbanes-Oxley Act. Nicknamed SOX (or Sarbox by some), this act has confused many a manager and implementation guru because of its broad, open-to-interpretation language. Having just gone through a major implementation at my workplace, I understand the worry and fear that accompanies this law. I hope my own experiences can shed a little light on this major, and often misunderstood, project.

If you are in the United States, or do business in the United States, and your company is over a certain size, you have to abide by a lot of regulatory laws - privacy, financial and documentation. SOX is a combination of financial and documentation. It consists of several different Titles, each of which establish just what is legal and illegal under the new act, but the gist of what applies to the IT world is the word "accountability".

Accountability in this context means documentation of established procedures, automation of reporting / tasks wherever possible and the ability to prove to an independent auditor that documented procedures are followed all the time. This includes financial reporting. Contrary to popular belief (and I was surprised by this one too), not all financial reporting have to be completely automated. There are instances where you can have financials in a spreadsheet that the accountants manipulate and change. The trick is to have this information documented. Always remember it's about "What, Why, Who and How".

The best way to start with a SOX implementation is to do a discovery phase. Find out what processes you currently have documented. Then list the ones you don't have documented. Processes include --but are not limited to-- security, access, permissions, reports, responsibilities and documentation location. I'll go through each of these one at a time.


This goes hand in hand with access and permissions. You need to list every single possible database, application, file share and server that you have control over. Then you need a list of who has any permissions or access to these items. This includes listing out Windows Groups (not the individual members), Windows Users, SQL Logins, or anyone who has local admin access to a Server.

Now there are certain circumstances where you won't have complete control over your security. For instance, if you're a DBA who does soft Admin tasks on a server where another team controls the hardware, you might not be able to keep them from adding groups or users to the Server's OS. Don't sweat it. Just find out the name of the team in question and include a notation in your documentation that says what you are responsible for and that Team X also has the ability to add logins to Server ABC.

Access & Permissions

Here we go with the hand in hand stuff I mentioned earlier. On the security list you just created, you need to include the access levels and permissions of each individual user / group. Since access and permissions aren't necessarily the same thing, I've listed them both.

Remember how Santa Claus checks his list twice? Creating the security / access / permissions list is just the beginning. Once you have it, you have to make a commitment (and document that commitment) to check the list on a regular basis, matching it against the reality of what security is actually implemented. You also have to fix problems as you find them and have a documented process for what to do when for the fixes.

Lastly, you have to have a documented process for granting permissions / access when someone joins the team or company and removing those permissions when someone leaves the team or company. And most important, you have to prove that you follow that procedures!

Here's a real life example. In my workplace, I wrote up the policy dictating who got what permissions in what SQL environments (and on what file shares) while a co-worker created a SQL job that appends the current S/A/P settings to an Excel sheet on a weekly basis. We double-check that sheet each Monday to see who has what and if anything violates the written policy. We then investigate how that permission got there (including the who) and then remove the permission. Sometimes, we even remove the permissions of the person who granted that access if it turns out that grantor is doing things outside the scope of his/her job duties. Lastly, we document those violations for later reference and tell the boss (so he doesn't get side-blinded when someone asks him about the violation).


This may sound silly, but SOX requires you to document your reports. The documentation for this should include the business rules used in generating the reports and, if necessary, an explanation of the math behind it. Also, if you have financial reports being generated by hand (data entry into a spreadsheet anyone?), you have to automate as much of the report as possible. Hand-keyed financials are no longer acceptable to the Feds unless the data in question is something that changes constantly, like an interest rate.

This is important to note. Not everything has to be automated when it comes to financial reporting. Just as much as possible. Then you have to document why you didn't automate something, what process is to be followed in the manual entry, who can make those manual entries and how those entries are to be made. And, again, you have to be able to prove to the auditor that someone isn't just making up numbers.

The general rule to this one is, start with the simple reports (working your way to the more complicated ones) and automate as many of them as you can. But make sure you write requirements on those reports as you work or an auditor may come along and make you redo the whole process.


Make sure you document who is responsible for what area of SOX implementation and follow-up. For example: if you have an internal Help Desk who is responsible for setting up Windows Logins, group memberships and permissions, don't forget to include them in your documentation (and your project). When asked, you need to be able to tell an auditor that it was the Help Desk that gave that user Domain Admin permissions.

And the auditors will ask. They want to see if you actually know the SOX processes as this gives them a good idea if you're even following them. After all, if you don't know the processes, chances are you aren't following them because you can't answer their questions.

So make sure to integrate your SOX documentation with that of all affected teams, to get their input and to document everyone's roles and responsibilities for tracking SOX violations. Doing this before the auditors show up will make your life that much easier.

Documentation Location

Last but not least, you have to document your documentation location.

Say what??? Yep, as strange as it sounds, you have to put down in your SOX documentation where you plan to store (electronically & physically) all the stuff you just wrote up. You also have to publish those locations to all affected team members. Auditors do actually ask you where your documents are and want you to prove you can get to them. And if you forget to tell one person about it, you get dinged severely in the audit.

Implementation Advice

Not all companies have to implement SOX. If you're a small business under a certain number of employees (and I don't know the number off the top of my head), you are exempt from SOX. It's always good to implement anyway under the notion that you'll improve your company security. And, if your company grows, you won't have to do it later on when life gets more complicated. Finally, when it comes to actually doing the project, there are a few "shortcuts" that might be available to you.

First, get your lists together of what might be affected. Meet with the entire team to weed out anything that doesn't really need to be done or add things that have been forgotten. Get a Project Manager to write up a project timeline and assign priorities to the different tasks. This is the most important thing you can do. If you get a surprise visit from the auditors and can prove that you're at least working on the issue (show them the project plan), they probably won't ding you as badly as if you didn't have anything done and had no proof you were in the middle of implementation.

Second, if you're in a large company, find out if other departments have done their SOX implementations yet. If so, they might be able to give you advice or a sample list of what they did. This could save you some time if you know what your own auditors are looking for.

Third, call the auditors! Beat them to the punch. Tell them you're in the middle of an implementation and you're stuck. You'd like advice on what you should be checking and, if your boss doesn't mind, can they come in and give you a pseudo-audit so you have an idea of what they are looking for. Believe it or not, the auditors will be more than happy to help you out. After all, it looks good on their records if you come out as a compliance All-Star.

Fourth, while it might be better to over-document than under-document, you still have to make sure that everyone on your team understands the documents. If they don't, you've just wasted an awful lot of time for nothing.

Last, there are rumors that SOX is due to expire soon, but skimming the text of the actual law, I don't see any expiration date listed. And since SOX compliance can actually help your business more than hurt it, I would recommend going through the processes anyway. Certainly you could clear away a lot of deadwood processes simply by finding out what would need to be documented for SOX. After all, why write documentation on a legacy report with no data and that no one ever looks at? Just delete the report and go on to the next thing.


SOX isn't actually as hard as a lot of people make it out to be. The main problem is that it is time and resource intensive, which means lots of big dollar signs if you have to pay people overtime or hire temps to help you out during the implementation. It's also very easy to let it slide when you have more pressing projects to complete. But if you document as you work on those other projects, you've just saved yourself time later down the road.


The Sarbanes-Oxley Act legal text can be found at

Wikipedia article

Total article views: 3627 | Views in the last 30 days: 3
Related Articles

Review: SQL Auditor

How solid is your SQL Server database design? Will it perform well in practice? SSW has a product, S...


Blowing off the SAS70 auditors

Don't you have to be forthcoming with security auditors?


How can I document reports (RDL) files?

Documenting or parsing reports RDL/RDS files