Compliance projects under Brazil’s data protection law (LGPD, Lei Geral de Proteção de Dados) tend to stall at a predictable point: the legal team asks what personal data the company processes, where it lives and who can access it, and nobody can answer with confidence. The answer sits in the systems and databases, and gathering it is technical work.
This article covers only that part. Defining purposes, choosing legal bases, writing privacy notices, deciding whether an impact assessment is needed and reviewing contracts are decisions for the legal team and the data protection officer. IT’s job is to give them an accurate, current picture of what exists.
Why start with an inventory
You cannot protect, delete or report to a data subject what you do not know you have. The LGPD requires data controllers and processors to keep records of their processing activities, and those records depend on information only the technical team has. Mapping also tends to surface risks worth fixing right away, regardless of any legal discussion.
Step 1: list everywhere data lives
Start broad. The list should include everything that stores or copies data, not just the core systems:
- database servers and instances, with every database on each;
- cloud systems and SaaS tools (CRM, ERP, payroll, customer service);
- BI databases, data warehouses and Power BI models;
- spreadsheets and exports on network shares and file services;
- copies of production used for development and testing;
- backups, with the retention period of each;
- application, web server and database logs.
Assign an owner to every item. An inventory without owners goes stale within months.
Step 2: find where the personal data is
Search by column name
On SQL Server, a catalog query gives you a first map. The patterns below combine English column names with the Portuguese ones common in Brazilian systems; adapt them to your own naming:
SELECT s.name AS schema_name,
t.name AS table_name,
c.name AS column_name,
ty.name AS data_type
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
WHERE c.name LIKE '%cpf%'
OR c.name LIKE '%email%'
OR c.name LIKE '%phone%'
OR c.name LIKE '%telefone%'
OR c.name LIKE '%birth%'
OR c.name LIKE '%nascimento%'
OR c.name LIKE '%address%'
OR c.name LIKE '%endereco%'
OR c.name LIKE '%name%'
OR c.name LIKE '%nome%'
ORDER BY schema_name, table_name, column_name;
It will return false positives (product_name matches “name”) and miss columns with generic names. It is a starting point, not the answer.
Sample the content
Column names can be misleading. Fields such as notes, free_text, extra_data or JSON columns often hold tax IDs, phone numbers or health details typed in by users. For those columns, look at a sample of the content, taking care that the analysis itself does not spread the data (no exporting samples to personal spreadsheets).
Record the classification in the database
SQL Server can store a classification directly on columns, and SSMS has a discovery and classification wizard that suggests columns based on their names:
ADD SENSITIVITY CLASSIFICATION TO dbo.Customer.TaxId
WITH (LABEL = 'Confidential', INFORMATION_TYPE = 'National ID', RANK = HIGH);
Classifications are stored in sys.sensitivity_classifications, where they can be queried and audited. Check that your SQL Server version supports this feature. In larger environments, catalog tools such as Microsoft Purview can scan many sources at once.
At this stage, flag sensitive personal data separately, such as health, biometric and racial or ethnic origin data. The law imposes stricter rules on it, and the legal team will need to know exactly where it is.
Step 3: follow the data
Personal data rarely stays in one place. It comes in through a form, lands in the application database, gets copied by an integration job to the data warehouse, shows up in a Power BI report, is exported to a spreadsheet and emailed to a supplier.
For each relevant database, record:
- where the data comes from (system, form, import, partner);
- where it is copied to (integrations, ETL, APIs, scheduled exports);
- which third parties receive or access it (software vendors, cloud providers, support).
A simple diagram of these flows helps the legal team identify processors and spot data sharing.
Step 4: who has access
Find out who can read personal data, not just who should. In the database, start with roles:
SELECT r.name AS role_name,
m.name AS member_name,
m.type_desc
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals AS m ON m.principal_id = rm.member_principal_id
ORDER BY role_name, member_name;
Common findings:
- applications and integrations using accounts with
db_ownerorsysadmin; - BI analysts with read access to every table, including those about people;
- reports published company-wide with tax ID, phone or salary columns;
- accounts belonging to former employees and former suppliers.
The technical controls for reducing exposure are well known: least privilege through roles, row-level security, encryption at rest (TDE) and in transit (TLS) and, for specific columns, Always Encrypted. Dynamic data masking helps hide values on screens and in reports, but it is not a security boundary against anyone who can run ad hoc queries.
Step 5: how long data is kept
Retention periods are set by the legal team, based on legal obligations and purpose. IT has to report how long data stays today and then implement whatever is decided.
Look for:
- tables that have never been purged and hold records going back to the system’s launch;
- backups and their retention periods (data deleted in production still exists in backups until they expire);
- development and test copies, which are often the oldest and least protected;
- logs, which also contain personal data such as IP addresses and user IDs.
When data has to go, there are two technical options: delete it or anonymize it. Mind the difference: pseudonymized data (with identifiers replaced by a code that can still be reversed) is still personal data.
Step 6: logs and traceability
After an incident, the first question is who accessed what, and when. Without records, the answer is a guess. On SQL Server, the audit feature records access to specific tables:
CREATE SERVER AUDIT PersonalDataAudit
TO FILE (FILEPATH = N'E:\Audit\');
ALTER SERVER AUDIT PersonalDataAudit WITH (STATE = ON);
USE Sales;
CREATE DATABASE AUDIT SPECIFICATION CustomerAccess
FOR SERVER AUDIT PersonalDataAudit
ADD (SELECT, UPDATE, DELETE ON OBJECT::dbo.Customer BY public)
WITH (STATE = ON);
Auditing everything produces more data than anyone can store or read. Start with the tables that hold sensitive data and the highest-risk operations. Decide where audit files are kept, who can read them and for how long, and protect that location against tampering.
What to hand over to the legal team
The output of the technical work is an organized inventory, which can start as a well-structured spreadsheet and grow into a data catalog. For each data set:
- system, database, table and columns;
- data category (identification, contact, financial, health and so on) and whether it is sensitive;
- whose data it is (customers, employees, suppliers, patients);
- source and destinations, including third parties;
- who has access and with what kind of permission;
- current retention and where copies exist;
- security measures already in place.
With that in hand, the legal team fills in purpose and legal basis, and both teams decide together what needs to change.
Need to map this across your systems?
If your compliance project has stalled for lack of technical information, an assessment helps size the mapping effort and prioritize the risks. See how our LGPD compliance service works or request an assessment.