What Is Data Masking? Masking vs De-Identification in Research

Data masking replaces or hides sensitive values, such as names, email addresses, identifiers or phone numbers, so that people who do not need the real values see a stand-in instead. In research it is most often used to let analysts, students or collaborators query a database without seeing direct identifiers. It is not the same as de-identification: NIST states that “not all tools that merely mask personal information provide sufficient functionality for performing de-identification” (NIST SP 800-188, final 14 September 2023, checked 29 October 2026).

Not what you are looking for? In clinical trials “masking” is another word for keeping participants or assessors unaware of group assignment; that is covered in blinding (masking) in clinical trials. This page is about masking data values in databases and datasets.

What it is used for in research

Research databases often hold direct identifiers (names, contact details, record numbers) next to the variables people actually analyse. Masking limits who can see those identifiers while the data stays in place. Typical research uses are giving a statistician or student a working copy of a table for exploration, letting a data manager view a table without seeing contact columns, and preparing a screen or export for a collaborator who is not cleared for identifiers.

Two points decide whether masking is enough for your situation:

• Masking hides values; it does not by itself make a dataset anonymous. Combinations of remaining fields (age, postcode, dates) can still single out a person. Judging that risk is the job of de-identification methods such as k-anonymity, covered in k-anonymity and disclosure risk in shared datasets and differential privacy. For the vocabulary, see anonymization vs pseudonymization and digitally anonymised data.
• Regulated identifier lists still apply. If your data falls under HIPAA, check the columns you mask against the 18 HIPAA identifiers, and see data anonymisation in research for the wider workflow.

Install or access (official source only)

This walkthrough uses dynamic data masking (DDM) in Microsoft SQL Server. Microsoft Learn describes it as a feature that “limits sensitive data exposure by masking it to nonprivileged users”, available in SQL Server 2016 (13.x) and later and in Azure SQL Database, and configured with Transact-SQL. You need an existing SQL Server or Azure SQL instance and a login that can alter tables. Product downloads and editions are on Microsoft’s own site.

For de-identification rather than masking, the open source ARX Data Anonymization Tool is downloaded from arx.deidentifier.org/downloads. The current version listed there on 29 October 2026 is 3.9.2 (last updated 21 September 2025). It is a platform-independent Java/SWT application with installers for Windows, macOS and Linux (64-bit); the runnable JARs need a current Oracle or OpenJDK Java Runtime Environment. Downloads are checksummed with a PGP-signed file.

Step-by-step task: mask a column in SQL Server

Commands below follow the Microsoft Learn page “Dynamic Data Masking” (page date 30 June 2025, checked 29 October 2026). The database is not changed by DDM: the stored data stays as it is and the mask is applied to query results.

1. Choose the column and the mask function. The available functions are default(), email(), random([start],[end]) (numeric columns only), partial(prefix,[padding],suffix), and, in SQL Server 2022 (16.x) only, datetime("Y"|"M"|"D"|"h"|"m"|"s").
2. Add the mask to an existing column. This needs the ALTER ANY MASK permission and ALTER permission on the table:

   ALTER TABLE Data.Membership ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"xxxx",0)');

   To define a mask when creating a table, put it in the column definition:

   Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NOT NULL

3. Create a test user with no login and give it read access:

   CREATE USER MaskingTestUser WITHOUT LOGIN;
   GRANT SELECT ON SCHEMA::Data TO MaskingTestUser;

4. Query as that user, then switch back:

   EXECUTE AS USER = 'MaskingTestUser';
   SELECT * FROM Data.Membership;
   REVERT;

   (EXECUTE AS USER is not supported in Fabric SQL database.)

5. Give the real values only to people who need them. “Grant the UNMASK permission to a user to allow them to retrieve unmasked data.” In SQL Server 2022 (16.x) UNMASK can be granted at database, schema, table or column level.
6. List what is masked:

   SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function
   FROM sys.masked_columns AS c
   JOIN sys.tables AS tbl ON c.[object_id] = tbl.[object_id]
   WHERE is_masked = 1;

7. Remove a mask if it is no longer needed:

   ALTER TABLE Data.Membership ALTER COLUMN LastName DROP MASKED;

Output and how to read it

A user without UNMASK sees the masked form; a user with UNMASK sees the stored value. What each function returns, per the Microsoft page:

Function	What a non-privileged user sees
default()	Strings show XXXX, numeric types show 0, dates show 1900-01-01 00:00:00.0000000
email()	The first letter, then [email protected] (the documented example form is [email protected])
random([start],[end])	A random number in the range given; numeric columns only
partial(prefix,[padding],suffix)	The first prefix and last suffix characters kept, with your padding string between them; for example partial(1,"XXXXXXX",0) turns 555.123.1234 into 5XXXXXXX

Read the output as a display control, not as a privacy guarantee. Microsoft is explicit that DDM “doesn’t protect against malicious intent to infer the underlying data”: a user who can run ad hoc queries can use range filters on a masked numeric column (for example a salary range narrowed to a single value) and learn from whether rows come back, even though the displayed value is masked. Microsoft describes DDM as complementary to auditing, encryption and row-level security, not a replacement for them.

Troubleshooting

• A privileged user sees real values. This is expected. CONTROL includes ALTER ANY MASK and UNMASK, so sysadmin and db_owner always see unmasked data. Test with a user that has only SELECT.
• Cannot add a mask to a column. Masks cannot be applied to Always Encrypted columns, FILESTREAM columns, COLUMN_SET or sparse columns that are part of a column set, computed columns, FULLTEXT key columns, or PolyBase external table columns.
• “ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.” Adding a mask is a schema change, so it cannot be done on a column with dependencies (for example a column referenced by a computed column or an index). Microsoft’s workaround is to remove the dependency, add the mask, then re-create the dependency; if an index is the dependency, drop the index, add the mask, and re-create the index.
• Masked data was copied into another table. SELECT INTO and INSERT INTO from a masked column copy the masked values, and an export made by a user without UNMASK contains masked data (the imported database then holds statically masked data). A copy made this way contains only the stand-in values, so do not expect to recover the originals from it.
• Updates still work. Masks do not stop a user with write permission from updating a masked column.

Free alternatives

Tool	What it does	Access	Source
ARX Data Anonymization Tool	Transforms structured (tabular) personal data using privacy models (k-anonymity, l-diversity, t-closeness, differential privacy) and transformations such as generalisation, suppression and microaggregation	Open source, Apache License 2.0; version 3.9.2	arx.deidentifier.org
SQL Server dynamic data masking	Masks column values in query results for non-privileged users; stored data unchanged	Built into SQL Server 2016 (13.x) and later and Azure SQL Database	Microsoft Learn

ARX works differently from DDM: it transforms a dataset and measures the result. Its graphical interface is organised into four perspectives. Configuration is where you import data with the import wizard, set transformation rules and choose privacy and quality models. Exploration lets you browse the space of possible solutions. Utility analysis compares input and output, including information loss and descriptive statistics. Risk analysis shows privacy risk metrics for both input and output. That last step is what a masking-only workflow does not give you. If your goal is to share a dataset outside the team, see the honest broker role for de-identified data.

FAQ

What is data masking?

It is the practice of hiding or replacing sensitive values with stand-in values so that users who do not need the real data see only the stand-ins. In SQL Server the dynamic form applies the mask to query results while the stored data stays unchanged.

Is data masking the same as anonymization?

No. NIST SP 800-188 notes that tools that merely mask personal information do not necessarily provide the functionality needed for de-identification. De-identification involves removing identifiers, transforming quasi-identifiers or generating synthetic data, and then assessing the remaining risk.

Can a masked column still be inferred?

Yes. Microsoft states that dynamic data masking does not protect against malicious attempts to infer the underlying data, for example through range queries by users with ad hoc query access.

Who can see the real values?

Users granted UNMASK, and anyone whose role includes it through CONTROL, such as sysadmin and db_owner.

Is “masking” in a clinical trial the same thing?

No. There it means keeping people unaware of treatment assignment; see blinding (masking) in clinical trials.

Sources (official docs)

• Microsoft Learn, Dynamic Data Masking (SQL Server); page date 30 June 2025; checked 29 October 2026.
• NIST, SP 800-188, De-Identifying Government Datasets: Techniques and Governance; final 14 September 2023; checked 29 October 2026.
• ARX Data Anonymization Tool, overview and downloads; checked 29 October 2026.