How to mask sensitive data in Azure SQL (Data Masking)

João Barros 05 de July de 2026 4 min read

Protecting personal data such as emails and card numbers is a basic data governance requirement — and it does not always mean rewriting your application. Dynamic Data Masking in Azure SQL hides sensitive columns in query results, showing the real values only to those who are allowed to see them. As a result, an analyst only sees aXXX@XXXX.com while the data in the table stays intact.

Prerequisites

A database in Azure SQL Database (or SQL Server 2022) where you have administrator permissions.
A query tool such as SQL Server Management Studio (SSMS) or Azure Data Studio.
Basic T-SQL knowledge (CREATE TABLE, SELECT, ALTER TABLE).

Step 1: Create a sample table

Let's start with a customer table that holds sensitive data. Run the following script to create it and insert a few rows:

CREATE TABLE dbo.Clientes (
    ClienteID   INT IDENTITY PRIMARY KEY,
    Nome        NVARCHAR(100),
    Email       NVARCHAR(100),
    Telemovel   VARCHAR(20),
    Cartao      VARCHAR(19)
);

INSERT INTO dbo.Clientes (Nome, Email, Telemovel, Cartao)
VALUES
 (N'Ana Silva',   'ana.silva@email.pt',   '912345678', '4111111111111111'),
 (N'Bruno Costa', 'bruno.costa@email.pt', '936000111', '5500005555555559');

Step 2: Apply masks to the sensitive columns

A mask is defined per column with the ADD MASKED WITH (FUNCTION = '...') clause. Each function hides the data in a different way. Let's mask the email, the phone number and the card:

-- email(): mostra a 1.a letra e o dominio -> aXXX@XXXX.com
ALTER TABLE dbo.Clientes
    ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

-- partial(): mostra apenas os ultimos 3 digitos -> XXXXXX678
ALTER TABLE dbo.Clientes
    ALTER COLUMN Telemovel ADD MASKED WITH (FUNCTION = 'partial(0,"XXXXXX",3)');

-- default(): esconde o valor por completo -> XXXX
ALTER TABLE dbo.Clientes
    ALTER COLUMN Cartao ADD MASKED WITH (FUNCTION = 'default()');

The partial(prefix, padding, suffix) function is the most flexible one: the first number says how many leading characters to keep, the quoted text is the visible padding, and the last number how many trailing characters to keep. In turn, default() applies the type's built-in mask — XXXX for text, 0 for numbers and a fixed date for dates.

Step 3: Create a user to test

Masks do not apply to administrators (members of db_owner), so you will not see any effect with your own account. To prove it, create a user without privileges and grant it read access only:

CREATE USER analista WITHOUT LOGIN;
GRANT SELECT ON dbo.Clientes TO analista;

Step 4: Confirm the mask with EXECUTE AS

You can temporarily impersonate that user to see the data exactly as they would see it, without switching sessions:

EXECUTE AS USER = 'analista';
SELECT Nome, Email, Telemovel, Cartao FROM dbo.Clientes;
REVERT;

The result shows emails as aXXX@XXXX.com, the phone number ending in its last digits and the card fully hidden. The data in the database is still intact — only the presentation changes.

Step 5: Grant exceptions with UNMASK

If a specific role needs to see the real values (for example, customer support), grant it the UNMASK permission. Since SQL Server 2022 and in Azure SQL you can also do it at column level:

-- Acesso total aos valores reais
GRANT UNMASK TO analista;

-- Ou apenas a uma coluna (granular, SQL Server 2022 / Azure SQL)
GRANT UNMASK ON dbo.Clientes(Email) TO analista;

To remove the exception later, use REVOKE UNMASK .... Always grant the minimum each role really needs.

Verify the result

To list every masked column and the function applied, query the sys.masked_columns system view:

SELECT t.name AS Tabela, c.name AS Coluna, c.masking_function AS Mascara
FROM sys.masked_columns AS c
JOIN sys.tables AS t ON c.object_id = t.object_id
WHERE c.is_masked = 1;

You should see the three columns with their masking function. If you run the SELECT again with EXECUTE AS after the GRANT UNMASK, the values now appear without a mask.

Conclusion

With just a few lines of T-SQL you protected sensitive data without touching the application or duplicating tables. The next step is to define your exception strategy: who really needs UNMASK and on which columns. Remember that masking is a presentation layer, not absolute security — always combine it with access control and, where possible, encryption. Which columns in your database deserve a mask today?