Forum Discussion

johnyhh's avatar
johnyhh
Copper Contributor
Nov 21, 2024

Data masking in SQL server 2019

We tried to mask a column in SQLDB

I first backup the table tblContacts to tblContacts_backup

then I issue following SQL command to mask the column "Email"

ALTER TABLE tblContacts
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'default()');

Then I check the result but not thing happen (no mask applied to the column)

compared tblContacts and tblContacts_backup but no different

SELECT Email
  FROM [ABC].[dbo].[tblContacts]
  where Emailis not null

SELECT Email
  FROM [ABC].[dbo].[tblContacts_backup]
  where Emailis not null

I have done following step but the problem still not solve

  1. make sure the user is not db admin role
  2. REVOKE UNMASK TO the user
  3. disconnect and connect again
  4. checked configure have been applied to the column

select
    c.name AS column_name,
    t.name AS data_type,
    m.is_masked,
    m.masking_function
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
LEFT JOIN sys.masked_columns m ON c.object_id = m.object_id AND c.column_id = m.column_id
WHERE t.name = 'tblContacts_backup' 

found is_masked = 1 and m.masking_function = default()

What else can I check or try?

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor

    Use `DENY` instead of `REVOKE`.

    Their are quite different but often make confused.

    `DENY` means refuse somebody to do something. That very clear.

    `REVOKE` is complicated, normally, it means cancel all `DENY` or `GRANT` assigned somebody on something before. Check this document REVOKE (Transact-SQL) - SQL Server | Microsoft Learn

Resources