Contents
In a company under SOX, the audit of IT general controls (ITGC) usually reaches the database team as a single request: "Send us the list of all production changes in the period." The team exports from the ticketing system and sends it. The auditor's second question is often unexpected: "How do you know this list is complete?" This article explains why the auditor asks for the population first, how the completeness of the population is shown, and what evidence is expected for each sampled change.
How the Change Control Is Tested
SOX 404 requires effective internal control over financial reporting. On systems that process financial data, the "program change" control assures that every change reaching production is authorised, tested and approved, and that the approved change is what was implemented. Database schema changes, stored procedures and direct data fixes are in scope. The test runs in three steps: the auditor asks for the list of all changes in the period (the population), selects a sample from it, and examines the evidence for each sample.
Why the Population Comes First
A sample drawn from an incomplete list proves nothing: a change that never got recorded cannot get into the sample either. That is why auditing standards require that, when information produced by the company is used as evidence, its accuracy and completeness are tested as well; in US public company audits this is the rule in PCAOB standard AS 1105. Such lists are called "information produced by the entity" (IPE). The change list is a typical IPE, and the auditor's first question is how it was produced: from which system, with which query, with which filter?
Where the Population Is Pulled From
| Source | What it contains | What it misses |
|---|---|---|
| Ticketing system | Changes that got a ticket | Everything without a ticket, which is exactly what the auditor is looking for |
| Pipeline logs | Deployments that went through the pipeline | Emergency fixes, data corrections, manual work |
| The database's own audit record | Schema changes actually made (if auditing is on) | Why, and with whose approval |
No single source is enough. The ticketing system holds the intent, the database holds the reality; completeness is shown by comparing the two. If SCHEMA_OBJECT_CHANGE_GROUP is on in SQL Server Audit, the actual schema changes of the period are pulled like this (CR create, AL alter, DR drop):
SELECT CAST(event_time AS date) AS ChangeDate, database_name, schema_name, object_name,
action_id, server_principal_name, statement
FROM sys.fn_get_audit_file(N'D:\Audit\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id IN ('CR', 'AL', 'DR')
AND event_time >= '2026-01-01' AND event_time < '2026-04-01'
ORDER BY event_time;
Two-Way Reconciliation
The reconciliation runs in two directions, and each direction catches a different risk:
- From reality to ticket: does every change made in the database have a ticket? An unmatched row is a change that went outside the process, and it is the most serious finding of the audit.
- From ticket to reality: does every ticket closed as "implemented" have a counterpart in the database? An unmatched ticket was either never applied, or applied at another time with other content.
The matching key is usually the object name, the database and the date; if the ticket number is written in the script comment or the request record, the match becomes exact. Every unmatched row needs a written explanation. An unexplained difference is, in the auditor's eyes, a sign that the control did not operate through the period.
Evidence for Each Sampled Change
Once the population is accepted, the auditor picks a sample and wants to see five things for each one:
- Request: who asked for the change, and why?
- Approval before implementation: is the approval time earlier than the execution time?
- Testing: was the change tried before production, and where is the result?
- Segregation of duties: are the requester, the approver and the implementer different people?
- Implemented = approved: is the text that ran in production the text that was approved?
The fifth item is the one most often missing: a ticket approval is usually given to a description, not to the final version of the script.
What the Product Adds, and Its Limit
The "Period Population" report in SQL Change Guard collects every request of the period from two separate sources, the request table and the audit record, and compares them row by row. Each row carries a PopulationCheck value: Match, NotInAuditTrail, NotInTable, StatusMismatch or InProgress; unmatched rows are listed first. The period package gives the same population as a signed file; the package covers at most 92 days, and if the event count limit is exceeded it is rejected rather than truncated. The evidence file of each sampled request holds the counterpart of the five items above: the requester and reason, the approval times, the isolated trial result (if one was run), the segregation of duties exceptions, and that the approved content carries the same fingerprint as the executed content.
The limit is clear: the product's population is the requests that went through the product. A change made outside the product is not on that list; for the "from reality to ticket" direction of the reconciliation, the database audit record above is still needed. The "Expected Access Principals" report lists the SQL account the product uses on each server; a schema change made by any other account came from outside the product.
Frequently Asked Questions
Who does SOX concern outside the US?
Companies whose securities trade on US exchanges, and their consolidated subsidiaries abroad. If a subsidiary's systems feed group financial reporting, the same ITGC tests run there too. Independent financial audits also test IT controls with a similar method; the population and sample logic is the same.
Do emergency changes belong in the population?
Yes, and they are often tested separately. With an emergency change the approval may come after implementation; the auditor then wants to see that a post-implementation review took place, within the defined time.
Who decides the sample size?
The auditor decides, based on the frequency and risk of the control. What you can influence is the cost of gathering the evidence for each sample; as that cost falls, wider testing and fewer findings become possible. See the related article for detail.
Are data fixes program changes too?
An UPDATE or DELETE that changes financial data directly is in scope even though it is not a code change; many audits test "direct data changes" as a separate control. The population should hold these alongside schema changes.
See the period population
We will show how the period list collected from two sources is compared, and how the five evidence items of a sampled request sit in one file.
Book a Demo →