Unable to Mask data after applying mask function in Azure Synapse table

Question

I have created table prd.CustomerDimension

CREATE TABLE [prd].[CustomerDimensions]
(
    [WWI Customer ID] [int] NOT NULL,
    [Customer] [nvarchar](100) NOT NULL,
    [Bill To Customer] [nvarchar](100) NOT NULL,
    [Category] [nvarchar](50) NOT NULL,
    [Buying Group] [nvarchar](50) NOT NULL,
    [Primary Contact] [nvarchar](50) NOT NULL,
    [Postal Code] [nvarchar](10) NOT NULL,
    [Valid From] [datetime2](7) NOT NULL,
    [Valid To] [datetime2](7) NOT NULL,
    [CreditLimit] [decimal](18, 2) NULL,
    [PhoneNumber] [nvarchar](20) NULL,
    [FaxNumber] [nvarchar](20) NULL
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED COLUMNSTORE INDEX
)
GO

Trying to apply Mask on column PhoneNumber using below query:

ALTER TABLE  [prd].[CustomerDimensions]
ALTER COLUMN [PhoneNumber] ADD MASKED WITH (FUNCTION = 'default()');

The query gets executed successfully but when i fetch data from the table it shows original data only no masking is applied.

Even applied mask function while creating table but still not able to mask column.

Please help me to figure this out.

Answer 1

It seems that the user has full rights to the database or at least she/he can get unmasked data.

Make sure that this user is not one of the 'SQL users excluded from masking'.

Quoted from docs:

Dynamic data masking permissions Dynamic data masking can be configured by the Azure SQL Database admin, server admin, or SQL Security Manager roles.

Dynamic data masking policy

• SQL users excluded from masking - A set of SQL users or Azure AD identities that get unmasked data in the SQL query results. Users with administrator privileges are always excluded from masking, and see the original data without any mask.

• Masking rules - A set of rules that define the designated fields to be masked and the masking function that is used. The designated fields can be defined using a database schema name, table name, and column name.

• Masking functions - A set of methods that control the exposure of data for different scenarios.