Cookie settings

Necessary, always on: they keep the site working and remember your cookie choice.

Skip to content
Skip to content

HIPAA. PHI discovery checklist.

Finding PHI outside the EHR: a checklist for HIPAA teams

Where electronic PHI collects outside the record system, the catalog queries that find it, and the evidence your risk analysis needs for each location.

Dipal Panchal

Co-founder and CEO · Updated November 8, 2026

On this page

The scope rule, and what you need to start

Applies to the PostgreSQL 16 to 18 and SQL Server 2022 catalogs, and to the file platforms you already search. The PostgreSQL queries were run in the Sylox lab on versions 16, 17 and 18; the SQL Server queries come from vendor documentation, which Sylox has not yet run. This guide is general information, not legal advice.

Scope rule: a location belongs in the risk analysis when electronic protected health information (ePHI) is stored, received, maintained or transmitted there. A system nobody has searched goes into the analysis as not searched, with an owner and a date.

The privacy officer and the security officer own the scope. A database administrator runs the catalog queries, and file platform administrators run the content searches. Plan a day per system for the first pass.

The checklist and its evidence

Work down the list for each system in scope. Every line ends with a record you can hand to an assessor.

The checklist and its evidence
StepWhat to doEvidence to keep
ScopeList every system that could hold ePHI, clinical or not, with an ownerA dated list of systems and owners
Structured dataSearch the catalog for candidate columns, then check valuesEach query, its result and its run date
FilesSearch shared folders and mailboxes; list scanned images apartSearch results by folder, with the sharing setting
ConfirmationDecide for each finding whether it is ePHI, and say whyA note per finding, rejected ones included
AccessRecord who and what can read each locationRole and group lists compared with approvals
ProtectionCheck stored values as well as platform settingsA sample check of the stored form
RecordsDecide delete, move, restrict or protect in place; repeat on changeDecisions and dates, kept six years

Under 45 CFR 160.103, protected health information is individually identifiable health information transmitted or maintained in any form or medium, with limited exclusions. ePHI is the part held or sent in electronic media.

Section 164.308(a)(1)(ii)(A) requires a thorough assessment of the potential risks and vulnerabilities to the ePHI an entity holds. HHS guidance on risk analysis reads that as all of an organization’s ePHI, whatever its medium, source or location.

A risk analysis that stops at the EHR leaves out the copies. HHS’s audit protocol for this specification looks for a defined scope that names the systems holding ePHI. The protocol has the assessor review that scope record.

Where ePHI collects outside the EHR

ePHI spreads through ordinary work: reporting, billing, quality review and system testing. Start with the places below, in whatever order your own systems suggest.

Files exchanged with outside parties leave copies on both sides, so include the shared folders and transfer locations you use with them.

  • Reporting databases and warehouse copies of clinical data
  • Billing, claims and revenue-cycle exports
  • Spreadsheets on shared drives, SharePoint and OneDrive
  • Email attachments, scanned forms and faxes saved as images
  • Test and development copies refreshed from production

Search structured data in six steps

Steps two and three carry the read-only queries. Keep every result with its date.

1. Agree the identifiers to look for

Owner
Privacy officer with the security officer
Tool or path
Your working definition of PHI
Expected output
A written list of column-name patterns and identifier shapes, for example patient, member, diagnosis, birth date, record number.
How to verify
The list is dated and signed off before the first query runs.

2. Find candidate columns in PostgreSQL

Owner
Database administrator
Tool or path
psql, any login that can connect
Command
SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 9, 2026, as a login with no grants beyond the PUBLIC defaults (queries 1 and 2); a role that can read the table (query 3), against synthetic data.
-- Read-only. pg_catalog lists every column whatever the table privileges.
-- 1. Anchor: tables and columns the catalog holds (compare with your inventory).
SELECT count(DISTINCT c.oid) AS tables, count(*) AS columns
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid AND c.relkind IN ('r','p')
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE a.attnum > 0 AND NOT a.attisdropped
  AND n.nspname NOT IN ('pg_catalog','information_schema') AND n.nspname !~ '^pg_toast';

-- 2. Candidate columns by name. reltuples is an estimate; -1 means never analyzed.
SELECT n.nspname AS schema_name, c.relname AS table_name, a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS data_type, c.reltuples::bigint AS est_rows
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid AND c.relkind IN ('r','p','v','m','f')
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE a.attnum > 0 AND NOT a.attisdropped
  AND n.nspname NOT IN ('pg_catalog','information_schema') AND n.nspname !~ '^pg_toast'
  AND a.attname ~* '(patient|mrn|member_?id|diagnos|icd_?10|birth|dob|ssn|npi|insur|claim|prescri|admit|discharge)'
ORDER BY 1, 2, 3;

-- 3. Shape check on one candidate (synthetic table): values that look like a US SSN.
SELECT count(*) AS rows_checked,
       count(*) FILTER (WHERE ssn ~ '^\d{3}-\d{2}-\d{4}$') AS ssn_shaped
FROM billing.claims_export;
Expected output
Query 1 returns one row of counts. Query 2 returns one row per candidate column. Query 3 returns the rows checked and how many have the shape.
How to verify
The table count in query 1 matches your inventory of PostgreSQL tables, or the difference is explained.

3. Find candidate columns in SQL Server

Owner
Database administrator
Tool or path
sqlcmd or SQL Server Management Studio
Command
SQLFrom Microsoft Learn documentation, page updated October 19, 2026; not yet tested by Sylox.
-- Read-only. Needs VIEW DEFINITION (else the catalog lists fewer objects, with no error)
-- and VIEW DATABASE PERFORMANCE STATE plus VIEW SECURITY DEFINITION for the row estimates.
SELECT COUNT(*) AS tables_visible FROM sys.tables;

SELECT s.name AS schema_name, o.name AS object_name, c.name AS column_name,
       ty.name AS data_type, p.row_count AS est_rows
FROM sys.columns AS c
JOIN sys.objects AS o ON o.object_id = c.object_id AND o.type IN ('U','V')
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
JOIN sys.types   AS ty ON ty.user_type_id = c.user_type_id
LEFT JOIN (SELECT object_id, SUM(row_count) AS row_count
           FROM sys.dm_db_partition_stats WHERE index_id IN (0,1) GROUP BY object_id) AS p
       ON p.object_id = o.object_id
WHERE LOWER(c.name) LIKE '%patient%' OR LOWER(c.name) LIKE '%mrn%'
   OR LOWER(c.name) LIKE '%diagnos%' OR LOWER(c.name) LIKE '%birth%'
   OR LOWER(c.name) LIKE '%ssn%'     OR LOWER(c.name) LIKE '%npi%'
   OR LOWER(c.name) LIKE '%insur%'   OR LOWER(c.name) LIKE '%claim%'
ORDER BY s.name, o.name, c.name;

-- Shape check on one candidate (synthetic table): values that look like a US SSN.
SELECT COUNT(*) AS rows_checked,
       SUM(CASE WHEN ssn LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN 1 ELSE 0 END) AS ssn_shaped
FROM billing.claims_export;
Expected output
One row per candidate column, with an estimated row count. The shape check returns two counts.
How to verify
Run the first query as a login with full metadata rights and compare the table count. A lower count for the review login means hidden objects.

4. Check values in each candidate column

Owner
Database administrator, with the privacy officer
Tool or path
A redacted sample from your own tooling
Expected output
For each candidate, a note on whether the values are health data tied to a person. Keep a five-row redacted sample apart from the result.
How to verify
A reviewer other than the administrator reads the notes and agrees or rejects each one.

5. Record the decision for each copy

Owner
Security officer, on the data owner’s advice
Tool or path
Your risk register
Expected output
One decision per copy: delete, move, restrict access or protect in place, with the date and the person who decided.
How to verify
Re-run the queries after the change. A deleted column is no longer listed by query 2.

Five findings, five decisions

Findings from one health system’s reporting and file stores. The provider number and the clinic phone line are rejected, with the reason.

Records
ValueDetected typeLabelReasonKept or rejected
987-65-4321US Social Security number shapeePHIIn billing.claims_export beside a diagnosis codeKept
MRN-00481516Medical record numberePHICopied from the EHR into a quality-review workbookKept
jane.doe@example.comEmail addressePHIIn a referral spreadsheet with a diagnosis columnKept
1234567893National Provider IdentifierNot ePHI hereNames a clinician in a directory table, no patient attachedRejected: provider identifier alone
555-0142Phone numberNot ePHI hereClinic switchboard line on a signage fileRejected: an organization number

Confirm it is PHI before you act

Individually identifiable health information relates to a person’s health, care or payment for care, and identifies the person or could reasonably be used to. Health terms with no identifier, or an identifier with no health context, need a closer look.

Information stops being PHI once it is de-identified under 164.514(b), by an expert’s determination or by removing the listed identifiers. A dataset with the names missing is not de-identified on that ground alone.

Record access and protection for each location

List who can read each location: users, roles, groups and service accounts. Section 164.312(a)(1) asks for access only by people or software programs granted access rights, and 164.308(a)(4) ties those rights to your authorization policies. Access nobody approved is a finding of its own.

Check encryption of stored ePHI under 164.312(a)(2)(iv) and of ePHI in transit under (e)(2)(ii). Both are addressable, which means you assess whether the safeguard is reasonable and appropriate, and implement it or document why not. Disk encryption does not travel with an export, so check the stored values of each copy.

Keep the record, and watch the proposed rule

Feed each decision into risk management under 164.308(a)(1)(ii)(B), and repeat the search when systems change, as part of the periodic evaluation in 164.308(a)(8). Keep the documentation for six years from creation or the date it last was in effect, whichever is later, under 164.316(b)(2)(i).

HHS proposed a Security Rule update on January 6, 2025 (90 FR 898). It would require a written technology asset inventory and a network map. As of November 9, 2026 the Federal Register lists no final rule. The current rule applies.

What this method misses

Column names find structure and nothing else. A free-text notes column named comments can hold a diagnosis, and a column named ssn can hold something else. The shape check also cannot separate a real number from a test value that fits the pattern.

The queries read tables and views. They miss PHI in file contents, scanned images, email bodies, backups and restored hosts. Read replicas keep their own catalog, so run the queries on each one.

Out of scope here: Privacy Rule uses and disclosures, breach notification, state health privacy laws, and substance use disorder records under 42 CFR Part 2.

With IRIS

IRIS finds sensitive data, including US health-related identifiers such as NPI, in the databases and files you connect, and records what it could not read. Capabilities vary by edition.

Where ePHI sits, copies included (164.308(a)(1)(ii)(A)): IRIS produces part of this, for the sources you connect and the data types IRIS detects.

Who can reach each ePHI store (164.308(a)(4)): IRIS produces part of this, where the source exposes permission data.

Plain or protected stored values (164.312(a)(2)(iv)): IRIS produces part of this, a check on sampled values, once activated on your install.

What each scan covered and could not read: IRIS produces this.

Who accessed ePHI, and when: IRIS produces part of this, where the source keeps an audit log.

Read the HIPAA page

Run it on one system and check the count

Pick the reporting database with the most clinical copies and run steps one to five on it. The pass test is simple: the table count matches your inventory, every candidate has a decision, and a second run shows the decided changes.

The next guide, questions auditors ask about sensitive data, lists the other questions an assessor may ask once this record exists.

Official sources

  1. 45 CFR 160.103: definitions, protected health information, eCFR. Opened November 8, 2026.
  2. 45 CFR 164.308: administrative safeguards, risk analysis and evaluation, eCFR. Opened November 8, 2026.
  3. 45 CFR 164.312: technical safeguards, eCFR. Opened November 8, 2026.
  4. 45 CFR 164.316: policies, procedures and documentation, six-year retention, eCFR. Opened November 8, 2026.
  5. 45 CFR 164.514: de-identification of protected health information, eCFR. Opened November 8, 2026.
  6. Guidance on risk analysis under the Security Rule, HHS Office for Civil Rights. Opened November 8, 2026.
  7. HIPAA audit protocol: Security Rule risk analysis, HHS Office for Civil Rights. Opened November 8, 2026.
  8. HIPAA Security Rule to strengthen the cybersecurity of ePHI, 90 FR 898, Federal Register. Opened November 8, 2026.
  9. pg_attribute system catalog, PostgreSQL 18, PostgreSQL documentation. Opened November 8, 2026.
  10. sys.columns (Transact-SQL), Microsoft Learn. Opened November 8, 2026.
Share this guide
FAQ

PHI discovery questions

Protect your brand. Start with your data.

A free assessment shows what sensitive data one database holds, who can reach it and what the scan could not check.