Build a production T-SQL audit script for your Student Information System. Ten articles, each covering a real compliance or data integrity domain — enrollment validation, FERPA, SAP, IPEDS, grading anomalies, referential integrity, and more. By Part 10, you'll have 57 checks running against live Jenzabar tables.
Every check is classified by severity. The final scorecard tells you exactly where to start.
Each article teaches one domain. By Part 10, they assemble into a single executable script.
FERPA data exposure, 1098-T readiness, IPEDS demographic completeness, and SAP/Title IV monitoring — all validated by SQL.
Orphaned course history, degree records without student masters, broken major codes — the structural cracks that corrupt every downstream report.
A formatted summary that prints CRITICAL, WARNING, and INFO totals with per-check row counts. Copy-paste into any audit memo.
Master switch for analysis mode, individual check selectors, trickle throttling for production, and full/curated record toggles.
Maps all 57 audit checks against the Microsoft Education Accelerator CDM (open-source). Shows what the standard models cover — and the compliance gaps they miss entirely.
Ten parts covering every domain in the audit script. Each article includes the T-SQL for its checks.
Why SIS databases decay without anyone noticing. Duplicate records accumulate, orphaned data breaks reports, compliance fields go blank — and nobody catches it until an auditor asks. The case for systematic, automated database auditing.
Active students with no course history. Duplicate NAME_MASTER records. Missing and malformed emails. The enrollment checks that catch the most common data entry failures — and the duplicate detection queries (name+DOB, SSN, SOUNDEX) that find merge candidates hiding in plain sight.
Students enrolled for a year without declaring a major. Multiple active degree records. Duplicate course enrollments, excessive credit loads, courses not in the catalog, and zero-credit anomalies. The academic backbone checks that registrars need but rarely run.
Historical records with no final grade. Grade changes where PREVIOUS_GRADE tells a different story. Letter grades on dropped courses — a CRITICAL that shouldn't exist. Plus: enrollments in year/term combinations that aren't defined, inactive courses still getting students, and catalog entries nobody has ever taken.
How many records store a full SSN? Are any duplicated across different people? Who's missing a date of birth, and whose DOB is physically impossible? Which students have FERPA restrictions that need to be honored in every downstream system? Plus: 1098-T readiness — students who can't receive tax forms because they're missing SSN or a mailing address.
Satisfactory Academic Progress is a Title IV requirement — and the data to monitor it is already in your SIS. GPA below 2.0, completion pace below 67%, exceeding 150% maximum time frame. Then: the withdrawal patterns that signal deeper problems — multiple W's in a term, complete term withdrawals that trigger R2T4 refund calculations, and serial withdrawers across 3+ terms.
Degrees conferred with fewer than 120 career hours. Graduates still flagged as active. Students enrolled 8+ years without a degree. Credit overloads and below-full-time flags. Transfer credits with suspicious letter grades. Repeated non-repeatable courses. Credit hour mismatches vs. catalog. Missing entrance years and course history that predates admission.
The structural checks: STUDENT_CRS_HIST rows with no STUDENT_MASTER, DEGREE_HISTORY orphans, NAME_MASTER without BIOGRAPH_MASTER, major codes that don't exist in MAJOR_MINOR_DEF. Then: IPEDS reporting readiness — missing gender, ethnicity, state, CIP codes, and a 5-year enrollment headcount trend analysis.
Elevated database roles, orphaned users, tables without primary keys. Then: the engineering that makes the script production-ready — the @@ROWCOUNT capture pattern, severity counters, the formatted scorecard, query analysis mode, individual check selectors, and trickle throttling for running against live databases without spiking load.
All 57 checks. 17 sections. One script. Configurable execution with @RunAll, @MasterSwitch, @CompleteRecord, and @Trickle. Prints a formatted scorecard with CRITICAL/WARNING/INFO totals. Tested against live Jenzabar production and test databases. Available at AITSQL.
This isn't theory. Every check runs against real tables with real data.
"We had 57,000 student records, fifteen years of course history, and no systematic way to know what was broken. Duplicate SSNs sat in production for years. Orphaned degree records corrupted IPEDS submissions. FERPA flags existed but nobody verified downstream systems honored them. So we built an audit script."