Cookie settings

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

Skip to content
Skip to content

Guide. DSAR. Databases.

How to respond to a DSAR across your databases

Find the columns that can hold the person’s data, search each table with the right identifiers and keep a log of what you searched and left out.

Dipal Panchal

Co-founder and CEO · Updated November 8, 2026

On this page

Four outcomes for every location, and a log that shows them

A data subject access request (DSAR) is mostly search work. The hard part isn’t the reply. It is knowing every table where the person’s data can sit and showing what you searched.

Give every location one of four outcomes: found, not found, not searched or failed. A location marked not searched carries a reason and an approver, and a location marked failed carries the error and a retry date.

  • Log the request and the date it arrived, because the clock starts there
  • Find the columns that can hold personal data, from the catalog and not from memory
  • Search with every identifier the person is known by, then with the internal keys the first pass returns
  • Record the outcome per location as counts, with no copied values
  • Have a second person check the response against the log before it goes out

Roles, prerequisites and time

The queries are for PostgreSQL 16, 17 and 18 and SQL Server 2022. Sylox ran the PostgreSQL script in its lab on versions 16, 17 and 18, on synthetic data. It has not run the SQL Server script, which carries the vendor-documentation label. A search of files, mailboxes and chat is outside this guide.

The privacy lead owns the request, the database administrator (DBA) runs the searches, and a second person checks the response. The column search takes minutes per database. The slow parts are owners’ answers about locations nobody mapped.

  • A read-only database account for each database in scope
  • The data inventory, or a list of systems that hold customer records
  • A restricted folder for the search log, apart from general query logs
  • The identifiers the person supplied, normalized as the guide shows

The clock and the search limit, law by law

What each law requires. The search method below is the same under all of them.

The clock and the search limit, law by law
LawResponse clockLimit on the searchSource
GDPR, Articles 12(3) and 15Without undue delay and within one month of receipt; two more months where needed, with reasons in the first monthArticle 15 states no limit; the controller shows what it searchedArticle 12, Article 15
UK GDPR, as amended by DUAA section 78One month from the relevant time (receipt, or later ID or fee); two more if needed, with notice in month oneA reasonable and proportionate search; treated as in force from January 1, 2024Section 78, Article 12A, inserted by section 76
CCPA and CPRA, Civil Code 1798.130Forty-five days from receipt; one extension of forty-five more, with noticeVerification and delivery rules sit in the same sectionSection 1798.130
India DPDP Act, sections 11 to 14Grievances answered within ninety days at most; the rights start on May 13, 2027Section 11 is a summary of personal data and processing for consent-based processingAct, Rule 14

Step 1: log the request and verify identity

Record the date the request arrived, who made it, what they asked for and the channel. Your deadline counts from receipt, so this date has to be beyond dispute. The law pages set out deadlines and scope law by law.

Record how you verified identity, and ask only for what you need to do that. Collecting more personal data to answer a request about personal data adds to the problem.

Step 2: find the columns that can hold the data

Start from your data inventory, then check it against the catalog. On PostgreSQL, read pg_attribute: unlike information_schema.columns, it lists columns of tables the running account has no privilege on. On SQL Server, INFORMATION_SCHEMA.COLUMNS returns only objects the account can see, so run it as an account with VIEW DEFINITION on the database.

Filter column names for email, phone, mobile, name, customer number and national identifiers. Names miss things. Free-text notes, JSON fields and comment columns hold personal data under names like notes or payload, so the second query lists those for sampling.

Read-only. Replace the pattern with the names your schemas use; the search values are reserved test values.

SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 9, 2026, as the postgres superuser, against synthetic data.
-- 1. Columns whose names suggest an identifier or personal data
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
FROM pg_attribute AS a
JOIN pg_class AS c ON c.oid = a.attrelid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relkind IN ('r', 'p', 'v', 'm', 'f')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
  AND a.attname ~* 'e_?mail|phone|mobile|msisdn|(first|last|full|sur)_?name|birth|dob|address|customer_?(id|no|num)|(^|_)pan($|_)|passport|national_?id'
ORDER BY 1, 2, 3;

-- 2. Free-text and JSON columns to sample by hand
SELECT n.nspname, c.relname, a.attname
FROM pg_attribute AS a
JOIN pg_class AS c ON c.oid = a.attrelid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE a.attnum > 0 AND NOT a.attisdropped AND c.relkind IN ('r', 'p')
  AND a.atttypid IN ('json'::regtype, 'jsonb'::regtype, 'text'::regtype)
  AND a.attname ~* 'note|comment|payload|description|body'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

-- 3. Search one table: counts only, normalized email and mobile number (psql -v email=… -v mobile=…)
SELECT count(*) AS matching_rows FROM crm.customer
WHERE lower(btrim(email)) = lower(btrim(:'email'))
   OR regexp_replace(mobile, '[^0-9]', '', 'g') LIKE '%' || :'mobile';
-- Example values: jane.example@example.com and 7700900123 (a number the UK regulator reserves for drama)

Read-only. Run in each database in scope, with VIEW DEFINITION on it and SELECT on the tables you search.

SQLFrom Microsoft SQL Server documentation, page updated October 19, 2026; not yet tested by Sylox.
-- 1. Columns whose names suggest an identifier or personal data
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE LOWER(COLUMN_NAME) LIKE N'%email%' OR LOWER(COLUMN_NAME) LIKE N'%phone%'
   OR LOWER(COLUMN_NAME) LIKE N'%mobile%' OR LOWER(COLUMN_NAME) LIKE N'%name%'
   OR LOWER(COLUMN_NAME) LIKE N'%birth%' OR LOWER(COLUMN_NAME) LIKE N'%address%'
   OR LOWER(COLUMN_NAME) LIKE N'%customer%id%' OR LOWER(COLUMN_NAME) = N'pan'
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;

-- 2. Free-text and JSON columns to sample by hand
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN (N'nvarchar', N'varchar', N'text', N'ntext')
  AND (LOWER(COLUMN_NAME) LIKE N'%note%' OR LOWER(COLUMN_NAME) LIKE N'%comment%'
    OR LOWER(COLUMN_NAME) LIKE N'%payload%' OR LOWER(COLUMN_NAME) LIKE N'%description%');

-- 3. Search one table: counts only
DECLARE @email nvarchar(320) = N'jane.example@example.com';
SELECT COUNT(*) AS matching_rows FROM crm.customer WHERE LOWER(LTRIM(RTRIM(email))) = LOWER(@email);

Step 3: choose identifiers and join keys

Search with every identifier the person gave you and any you hold: email addresses, mobile numbers, account or customer numbers. Normalize first. Trim and lowercase email addresses, and strip a mobile number to digits so the country code or a leading zero doesn’t hide a match, as query 3 does.

A first pass usually returns the person’s internal keys. Use those keys for a second pass through tables that don’t hold the email, such as orders, payments and support history. Record the key you used beside each result, because the key is what links the table to the person.

Step 4: search each location and log the outcome

Run the searches with a read-only account. Return counts, not values, to the log: the values go into the response, and the log proves the search ran. Search queries contain the person’s identifiers, and those queries can end up in database logs, so keep the search log in a restricted location and keep identifiers out of tickets and chat.

Check each count against a known record. Search your own test person, seeded in each table, and confirm the search finds it. A search that cannot find a record you know is there has not searched.

Cover the copies too: read replicas, analytics warehouses and test databases fed from production. Production data in test databases shows how to find those. Record your position on backups and archives, and who approved it.

What the search log looks like

One request, searched by email address and mobile number. The legacy archive was not searched, with an approver and a date.

Records
SourceStatusReasonOwner
crm.customer (PostgreSQL)FoundEmail and mobile matched one row; internal key recorded for the second passCRM database administrator
billing.orders (PostgreSQL)FoundSearched by internal key; matching rows counted, values go into the responseBilling database administrator
support.tickets (SQL Server)Not foundEmail and key searched, no rows; the seeded test record was foundSupport platform owner
analytics.events (warehouse)FailedConnection refused on November 10, 2026; retry set for November 11, 2026Data platform lead
Legacy archive (tape export)Not searchedExcluded: no query path; the CISO approved the exclusion on November 9, 2026 and recorded the reasonPrivacy lead

Step 5: assemble the response and have it checked

Pull the records found into the response, with the information the law requires. Have someone other than the searcher check that it covers each location marked found, and that every location marked not searched has its reason on file.

Review the output for other people’s data, such as names in support notes, before it goes out. Record each redaction and the reason for it. Keep the response exactly as sent, because you may be asked to show it later.

What this search misses

Each gap can leave a person’s data out of the response. Name the ones that apply in the request record.

What this search misses
GapWhat to do about it
Columns with unhelpful namesA column called f12 matches no pattern; ask the system owner for a data dictionary and add its names
Data inside files and mailboxesNeither query reads them; search those stores with their own tools and log them as separate locations
Identifiers stored in changed formAn email stored as a keyed hash matches only after the same hash is applied to the search value
Archives, backups and replicasThey hold their own copies; record whether each was searched or excluded, with the approver
Rows you may not have visibility ofRow-level security can hide rows from the search account; check the policy before recording not found

The file to keep for each request

One record per request, kept with restricted access.

The file to keep for each request
Evidence itemProduced by, when and whereCompleteness checkSupports
The request, date received and identity checkPrivacy lead, at receiptThe clock start date matches the channel’s timestampGDPR Article 12(3)
The column search output and the inventory it was checked againstDBA, at the start of the searchEvery system in the inventory has a database in the output, or an exclusionGDPR Article 15
The search log: location, identifier, outcome, dateDBA, per locationA seeded test record was found in each searched tableUK DUAA section 78
Exclusions, each with a reason and an approverPrivacy lead and CISONo location is silentGDPR Article 5(2)
The response as sent, with each redaction explainedPrivacy lead, at sendA second person signed the checkGDPR Article 15(4)
With IRIS

IRIS logs the request and searches the personal-data locations your stewards confirmed in connected databases. Your team makes any change in the source system. Capabilities vary by edition.

One record for each access, correction and erasure request, with its history: IRIS produces this.

A search by email address, mobile number or PAN: IRIS produces part of this, confirmed locations in connected databases.

Locations searched and excluded, with the reason for each: IRIS produces this.

Files and mailboxes: Not produced by IRIS.

The correction or erasure in the source system: Not produced by IRIS.

The response as a PDF, with each version kept: IRIS produces this.

See how DSAR search works

Test the log on your last request

Take the last request you answered and try to rebuild its search log from what is on file. Every location with no outcome is a gap in the record, and the first thing an auditor would ask about.

Seed a test person in each table the next request will touch, so the check in step 4 has a known record to find. In a UK GDPR assessment, IRIS maps its evidence to those controls through its unified compliance framework. A scored readiness view for DSAR work isn’t in v1; ask us if you need one.

The next guide covers the question that follows a response: what you can show an auditor about the change you made afterward, in How to show an auditor that an exposure was closed.

Official sources

  1. GDPR Article 12, transparent information, communication and modalities, EUR-Lex. Opened November 8, 2026.
  2. GDPR Article 15, right of access by the data subject, EUR-Lex. Opened November 8, 2026.
  3. Data (Use and Access) Act 2025, section 78, legislation.gov.uk. Opened November 8, 2026.
  4. Data (Use and Access) Act 2025, section 76, Article 12A time periods, legislation.gov.uk. Opened November 9, 2026.
  5. California Civil Code section 1798.130, response time limits, California Legislative Information. Opened November 8, 2026.
  6. Digital Personal Data Protection Act, 2023, sections 11 to 14, MeitY, Government of India. Opened November 8, 2026.
  7. Digital Personal Data Protection Rules, 2025, Rule 14, Gazette of India. Opened November 8, 2026.
  8. PostgreSQL 18: pg_attribute, PostgreSQL Global Development Group. Opened November 8, 2026.
  9. COLUMNS (Transact-SQL), Microsoft Learn. Opened November 8, 2026.
Share this guide
FAQ

DSAR search questions

Protect your brand. Start with your data.

Bring your last three requests. We’ll show you what their search log would look like in IRIS.