Dynamic Data Masking in SQL Server

4111111111111111

4111XXXXXX1111

Dynamic Data Masking – How It Works

Stored Data
    |
    | 4111111111111111
    v
SQL Server
    |
    +----------------------+
    |                      |
    v                      v
Standard User        Authorized User
    |                      |
    v                      v
4111XXXXXX1111       4111111111111111

Stored PAN
4111111111111111

        ↓

Dynamic Data Masking

        ↓

Standard User
4111XXXXXX1111

Step-by-Step Dynamic Data Masking Implementation

Create the Demo Database

For this demonstration, use a dedicated test database.

USE master;
GO

CREATE DATABASE PCI_DDM_DEMO;
GO

USE PCI_DDM_DEMO;
GO

Create the Customer Card Table

Create a table containing customer and payment card information.

CREATE TABLE dbo.CustomerCard
(
    CustomerID       INT IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_CustomerCard PRIMARY KEY,

    CustomerName     VARCHAR(100) NOT NULL,
    CardNumber       CHAR(16) NOT NULL,
    CardExpiry       CHAR(5) NULL,
    CardStatus       VARCHAR(20) NULL
);
GO

Column Classification

Insert Approved Test Data

Insert approved test data into the table.

INSERT INTO dbo.CustomerCard
(
    CustomerName,
    CardNumber,
    CardExpiry,
    CardStatus
)
VALUES
(
    'Test Customer 01',
    '4111111111111111',
    '12/29',
    'ACTIVE'
),
(
    'Test Customer 02',
    '5555555555554444',
    '06/30',
    'ACTIVE'
),
(
    'Test Customer 03',
    '6011111111111117',
    '09/28',
    'ACTIVE'
);
GO

View Data Before Masking

Before applying DDM, query the table.

SELECT
    CustomerID,
    CustomerName,
    CardNumber,
    CardExpiry,
    CardStatus
FROM dbo.CustomerCard;
GO

Example Result

CustomerID    CustomerName       CardNumber          CardExpiry    CardStatus
----------    ----------------   ----------------    ----------    ----------
1             Test Customer 01   4111111111111111    12/29         ACTIVE
2             Test Customer 02   5555555555554444    06/30         ACTIVE
3             Test Customer 03   6011111111111117    09/28         ACTIVE

Apply Dynamic Data Masking

Apply a partial mask to the CardNumber column.

ALTER TABLE dbo.CustomerCard
ALTER COLUMN CardNumber
ADD MASKED WITH
(
    FUNCTION = 'partial(4,"XXXXXX",4)'
);
GO

The masking function is designed to expose the first four and last
four characters.

Example

Original:
4111111111111111

Masked:
4111XXXXXX1111

The stored value remains unchanged.

Validate DDM Configuration

SQL Server exposes masking configuration through
sys.masked_columns.

SELECT
    DB_NAME() AS DatabaseName,
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    c.is_masked,
    c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS t
    ON c.object_id = t.object_id
WHERE c.is_masked = 1;
GO

Example Result

Create Standard Card Data Role

Create a role for users who need access to the table but do not need
to see the complete card number.

CREATE ROLE Card_Data_User;
GO

GRANT SELECT
ON dbo.CustomerCard
TO Card_Data_User;
GO

The role has table access but does not have UNMASK.

Create Authorized Card Data Role

Create a separate role for users who may require approved access
to unmasked card data.

CREATE ROLE Card_Data_Authorized;
GO

GRANT SELECT
ON dbo.CustomerCard
TO Card_Data_Authorized;
GO

At this point, the role has SELECT but does not yet have
UNMASK.

Create Standard Application User

CREATE USER Demo_ApplicationUser
WITHOUT LOGIN;
GO

ALTER ROLE Card_Data_User
ADD MEMBER Demo_ApplicationUser;
GO

Access Model

Demo_ApplicationUser
        |
        v
Card_Data_User
        |
        v
SELECT
        |
        v
Masked CardNumber

Create Authorized Card Operations User

CREATE USER Demo_CardOperations
WITHOUT LOGIN;
GO

ALTER ROLE Card_Data_Authorized
ADD MEMBER Demo_CardOperations;
GO

At this stage, the user has SELECT access but no
UNMASK permission.

Test Standard User After Masking

Execute the query using the standard user security context.

EXECUTE AS USER = 'Demo_ApplicationUser';
GO

SELECT
    CustomerID,
    CustomerName,
    CardNumber,
    CardExpiry,
    CardStatus
FROM dbo.CustomerCard;
GO

REVERT;
GO

Expected Result

CustomerID    CustomerName       CardNumber        CardExpiry    CardStatus
----------    ----------------   --------------    ----------    ----------
1             Test Customer 01   4111XXXXXX1111    12/29         ACTIVE
2             Test Customer 02   5555XXXXXX4444    06/30         ACTIVE
3             Test Customer 03   6011XXXXXX1117    09/28         ACTIVE

The important observation is:

SELECT permission
        +
No UNMASK permission
        =
Masked CardNumber

Grant UNMASK to the Authorized Role

If an authorized user has a legitimate business requirement to see
the original card number, UNMASK can be granted to the
approved role.

GRANT UNMASK
TO Card_Data_Authorized;
GO

Test Authorized User

EXECUTE AS USER = 'Demo_CardOperations';
GO

SELECT
    CustomerID,
    CustomerName,
    CardNumber,
    CardExpiry,
    CardStatus
FROM dbo.CustomerCard;
GO

REVERT;
GO

Expected Result

CustomerID    CustomerName       CardNumber          CardExpiry    CardStatus
----------    ----------------   ----------------    ----------    ----------
1             Test Customer 01   4111111111111111    12/29         ACTIVE
2             Test Customer 02   5555555555554444    06/30         ACTIVE
3             Test Customer 03   6011111111111117    09/28         ACTIVE

Authorized Access Model

Demo_CardOperations
        |
        v
Card_Data_Authorized
        |
        +---- SELECT
        |
        +---- UNMASK
        |
        v
Original CardNumber

Revoke UNMASK

When the business requirement no longer exists, revoke
UNMASK.

REVOKE UNMASK
FROM Card_Data_Authorized;
GO

Test again:

EXECUTE AS USER = 'Demo_CardOperations';
GO

SELECT
    CustomerID,
    CustomerName,
    CardNumber,
    CardExpiry,
    CardStatus
FROM dbo.CustomerCard;
GO

REVERT;
GO

Expected Result

4111XXXXXX1111
5555XXXXXX4444
6011XXXXXX1117

This demonstrates that the same authorized role returns to masked
visibility after UNMASK is revoked.

Review DDM Permissions

Review the permissions associated with the DDM roles.

SELECT
    dp.name AS PrincipalName,
    dp.type_desc,
    p.permission_name,
    p.state_desc
FROM sys.database_principals AS dp
LEFT JOIN sys.database_permissions AS p
    ON dp.principal_id = p.grantee_principal_id
WHERE dp.name IN
(
    'Card_Data_User',
    'Card_Data_Authorized'
)
ORDER BY
    dp.name,
    p.permission_name;
GO

Expected Access Model

Review Role Membership

Review which users belong to the DDM roles.

SELECT
    RoleName = r.name,
    MemberName = m.name
FROM sys.database_role_members AS rm
INNER JOIN sys.database_principals AS r
    ON rm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals AS m
    ON rm.member_principal_id = m.principal_id
WHERE r.name IN
(
    'Card_Data_User',
    'Card_Data_Authorized'
)
ORDER BY
    r.name,
    m.name;
GO

This is useful for access reviews and audit evidence.

————– ——————— —-test end—————————– ————————

Validation Checklist

Pre-Implementation

• ☐ Business requirement approved.
• ☐ Data Owner approval obtained.
• ☐ Sensitive data identified.
• ☐ PCI applicability assessed.
• ☐ Approved test data confirmed.
• ☐ Application impact assessed.
• ☐ Users requiring unmasked access identified.
• ☐ Change approval obtained where required.
• ☐ Rollback plan prepared.

Implementation

• ☐ Correct database confirmed.
• ☐ Correct table confirmed.
• ☐ Correct column confirmed.
• ☐ DDM masking function applied.
• ☐ sys.masked_columns validated.
• ☐ Standard role created.
• ☐ Authorized role created.
• ☐ Required SELECT permissions granted.
• ☐ Users assigned to appropriate roles.

Testing

• ☐ Data validated before masking.
• ☐ Standard user tested.
• ☐ Standard user receives masked PAN.
• ☐ UNMASK granted through approved role.
• ☐ Authorized user tested.
• ☐ Authorized user receives unmasked PAN.
• ☐ UNMASK revoked.
• ☐ Masked visibility revalidated.
• ☐ Application functionality validated.

Rollback Procedure

If DDM must be removed, execute:

ALTER TABLE dbo.CustomerCard
ALTER COLUMN CardNumber
DROP MASKED;
GO

Validate:

SELECT
    DB_NAME() AS DatabaseName,
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    c.is_masked,
    c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS t
    ON c.object_id = t.object_id
WHERE c.object_id = OBJECT_ID('dbo.CustomerCard');
GO

Rollback validation should confirm:

• Masking configuration is removed.
• Application functionality is validated.
• Access permissions are reviewed.
• Security implications are assessed.
• Change documentation is updated.

Change Management

Production DDM changes should follow the organization’s approved
Change Management process.

The change request should include:

• Business justification.
• Database and SQL Server instance.
• Schema, table, and column.
• Data classification.
• PCI applicability.
• Masking function.
• Affected users and roles.
• UNMASK requirement, if applicable.
• Security assessment.
• Application impact assessment.
• Implementation plan.
• Validation plan.
• Rollback plan.
• Required approvals.

Audit Evidence

Recommended evidence includes:

• Approved change request.
• Business/Data Owner approval.
• Security approval where applicable.
• PCI assessment where applicable.
• Pre-change configuration.
• Implementation script.
• sys.masked_columns output.
• Standard-user validation.
• Authorized-user validation.
• UNMASK approval.
• Role membership evidence.
• Permission review evidence.
• Application validation.
• Rollback evidence where applicable.

DDM Configuration Evidence Query

SELECT
    DB_NAME() AS DatabaseName,
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    c.is_masked,
    c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS t
    ON c.object_id = t.object_id
WHERE c.is_masked = 1;
GO

Periodic Review

DDM configuration and access should be periodically reviewed according
to organizational security and access governance requirements.

The review should confirm:

• ☐ Sensitive columns remain correctly classified.
• ☐ DDM remains required.
• ☐ Masking function remains appropriate.
• ☐ Standard users still require access.
• ☐ Authorized users still require access.
• ☐ UNMASK access remains justified.
• ☐ Unnecessary UNMASK permissions are revoked.
• ☐ Role memberships remain accurate.
• ☐ Audit evidence is available.
• ☐ PCI/security requirements remain applicable and satisfied.

Troubleshooting

User Can See the Full Card Number

Check whether the user or an applicable role has UNMASK.

SELECT
    dp.name AS PrincipalName,
    dp.type_desc,
    p.permission_name,
    p.state_desc
FROM sys.database_principals AS dp
LEFT JOIN sys.database_permissions AS p
    ON dp.principal_id = p.grantee_principal_id
WHERE p.permission_name = 'UNMASK';
GO

Also review:

• Role membership.
• User context.
• Elevated privileges.
• Application connection identity.

User Cannot Access the Table

Verify:

• Database user exists.
• User is a member of the correct role.
• Role has SELECT.
• Correct database is being queried.
• Object name and schema are correct.

Application Stops Working After DDM

Possible causes include:

• Application expects the original value.
• Application performs validation against the returned value.
• Application uses the card number in business logic.
• Application service account does not have the expected access.

Recommended Approach

1. Review application logs.
2. Identify the application connection account.
3. Validate its effective permissions.
4. Confirm whether unmasked access is genuinely required.
5. Do not grant UNMASK without an approved requirement.
6. Modify the application or masking approach where appropriate.
7. Roll back the change if required to restore service.

DDM Configuration Is Not Visible

Run:

SELECT
    c.name,
    c.is_masked,
    c.masking_function
FROM sys.masked_columns AS c
WHERE c.object_id = OBJECT_ID('dbo.CustomerCard');
GO

Confirm:

• Correct database.
• Correct schema.
• Correct table.
• Correct column.
• Successful execution of the masking statement.
• SQL Server error messages.

Troubleshooting Escalation Matrix

Security Best Practices

The following practices should be followed when implementing DDM.

Use Least Privilege

Do not grant users more access than required.

Separate Masked and Unmasked Access

Use different roles where practical.

Card_Data_User
    |
    +-- SELECT
    |
    +-- Masked Data

Authorized access:

Card_Data_Authorized
    |
    +-- SELECT
    +-- UNMASK
    |
    +-- Original Data

Control UNMASK Access

UNMASK should be treated as sensitive access and should be:

• Business justified.
• Approved.
• Restricted.
• Periodically reviewed.
• Removed when no longer required.

Do Not Treat DDM as Encryption

DDM controls the presentation of data; it does not protect the
underlying value in the same way as encryption.

Combine DDM With Other Security Controls

Depending on the environment and requirements, consider:

• Least privilege.
• Role-Based Access Control.
• SQL Server Audit.
• Encryption.
• Always Encrypted where appropriate.
• Tokenization where appropriate.
• Application security.
• Network security.
• Monitoring.
• Periodic access review.

DDM vs Encryption

Key Takeaways

Dynamic Data Masking is a useful SQL Server security feature for
reducing unnecessary exposure of sensitive information.

Standard User

User + SELECT
      |
      v
No UNMASK
      |
      v
Masked Data

Authorized User

User + SELECT + UNMASK
      |
      v
Original Data

After UNMASK Is Revoked

User + SELECT
      |
      v
Masked Data

Summary

This SQL Server Dynamic Data Masking implementation demonstrates how
organizations can provide controlled access to sensitive payment card
information while reducing unnecessary exposure of complete card numbers.

The demonstrated design provides:

• Role-based access.
• Masked visibility for standard users.
• Controlled unmasked visibility for authorized users.
• Explicit UNMASK management.
• Permission and role validation.
• Audit evidence.
• Rollback capability.
• Periodic access review.
• PCI and security considerations.

General Disclaimer

References

• Microsoft SQL Server Dynamic Data Masking documentation.
• Microsoft SQL Server documentation for sys.masked_columns.
• Microsoft SQL Server documentation for database roles and permissions.
• Microsoft SQL Server documentation for UNMASK.
• PCI Security Standards Council – PCI DSS and applicable guidance.
• Organization Information Security Policy.
• Organization Data Classification Policy.
• Organization Access Management Policy.
• Organization Change Management Policy.