Native Masking — Data Appears Unmasked¶

A column has a Default (or other) masking policy applied in Privacera, and the user or group has only read access with no explicit unmasking policy. When querying the table in SQL Server Management Studio (SSMS) or another SQL client, the data still appears unmasked.

This is usually not a connector apply failure. The login session running the query often has privileges that bypass SQL Server Dynamic Data Masking.

Symptoms¶

• Privacera shows a column-level masking policy (for example Default masking) applied successfully.
• The user and group policies show only SELECT / read access — no unmasking grant in Privacera.
• Querying the masked column in an on-premises SQL Server environment returns the original (unmasked) values.
• PolicySync ACCESS audit shows masking was applied (MASK / masking function created on the column).

Explanation¶

SQL Server Dynamic Data Masking hides column values only for principals that do not have bypass privileges. The following principals typically see unmasked data even when a mask is defined on the column:

If you connect to SQL Server with an administrator account (or an account that maps to dbo), you will see unmasked data even though regular users and groups with read-only access see masked values.

Troubleshooting Steps¶

Run the following queries in SSMS while connected with the same login used to reproduce the issue. Replace <database> with the managed database name.

Step 1 — Check the effective login and database user¶

1
2
3
4
5

USE [<database>];

SELECT
    SUSER_NAME() AS LoginName,
    USER_NAME() AS DatabaseUser;

If DatabaseUser is dbo, the session has database-owner-level access and masking is bypassed.

Step 2 — Check for sysadmin membership¶

1

SELECT IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin;

A result of 1 means the login is a member of sysadmin and will see unmasked data.

Step 3 — Check database roles for the current user¶

1
2
3
4
5
6
7
8

SELECT
    dp.name AS RoleName
FROM sys.database_role_members drm
JOIN sys.database_principals dp
    ON drm.role_principal_id = dp.principal_id
JOIN sys.database_principals dp2
    ON drm.member_principal_id = dp2.principal_id
WHERE dp2.name = USER_NAME();

If the user is a member of db_owner, masking is bypassed.

Step 4 — Check for UNMASK permission¶

1

SELECT HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'UNMASK') AS HasUnmaskPermission;

1
2
3
4
5
6
7

SELECT HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'UNMASK') AS HasDatabaseUnmask;

SELECT HAS_PERMS_BY_NAME(
    '<schema>.<table>.<column>',
    'OBJECT',
    'UNMASK'
) AS HasColumnUnmask;

Step 5 — Combined diagnostic query¶

1
2
3
4
5
6
7

USE [<database>];

SELECT
    SUSER_NAME() AS LoginName,
    USER_NAME() AS DbUser,
    IS_SRVROLEMEMBER('sysadmin') AS IsSysAdmin,
    HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'UNMASK') AS HasUnmask;

Step 6 — Test masking as a specific non-admin user¶

If Steps 1–5 show admin-level access, impersonate the target user to confirm masking works for that principal:

1
2
3
4
5
6
7

USE [<database>];

EXECUTE AS USER = 'DOMAIN\username';   -- Replace with the on-premises user or group mapped login

SELECT <masked_column> FROM <schema>.<table>;

REVERT;

After EXECUTE AS USER, masked columns should return obfuscated values (for example XXXX for string default masking). If data is masked under impersonation but unmasked under the original login, the original login has bypass privileges — not a Privacera policy issue.

Common Root Causes¶

Verify Masking Is Applied in SQL Server¶

Confirm the mask exists on the column independently of the querying user:

1
2
3
4
5
6
7
8
9

SELECT
    t.name AS TableName,
    c.name AS ColumnName,
    mc.masking_function,
    mc.is_masked
FROM sys.masked_columns AS mc
JOIN sys.tables AS t ON mc.object_id = t.object_id
JOIN sys.columns AS c ON mc.object_id = c.object_id AND mc.column_id = c.column_id
WHERE t.name = '<table>';

If is_masked is 1, the mask is defined in SQL Server. The issue is with the querying principal's privileges, not mask creation.

Recommended Action¶

1. Validate masking using a non-admin login that matches the Privacera user or group principal.
2. Use EXECUTE AS USER to confirm masked output for that principal when admin accounts must be used for initial connection.
3. Work with the customer DBA to review sysadmin membership and UNMASK grants for test accounts.
4. Confirm no unmasking policy exists in Privacera for the user or group under test. On SQL Server 2019 and earlier, unmasking is granted at the database level — a database-level unmask policy affects all columns in that database.