Forum Discussion
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 nullSELECT Email
FROM [ABC].[dbo].[tblContacts_backup]
where Emailis not null
I have done following step but the problem still not solve
- make sure the user is not db admin role
- REVOKE UNMASK TO the user
- disconnect and connect again
- 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?
- rodgerkongIron 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