SQL Server Dynamic Data Masking is a feature that allows you to obscure sensitive data from non-privileged accounts, improving data security and compliance. Rather than showing credit card numbers, passwords, or personal identifiers in cleartext, you can define masking rules at the column level so that specific users see only masked values. In contrast, others with elevated permissions see the actual data.

When to Use Dynamic Data Masking

Lower environments (development, QA). Typically, developers and testers do not need access to actual sensitive information. Masking ensures that they can work with realistic datasets without risking exposure to PII.
Third-party access. When sharing data with external consultants or analytics vendors, masked data prevents inadvertent or malicious disclosure of sensitive content.
Regulatory compliance. For environments where regulations like GDPR, HIPAA, or PCI-DSS apply, dynamic masking helps ensure only authorized personnel can view sensitive data in cleartext

Prerequisites

SQL server version. Dynamic data masking is available in SQL Server 2016 and later.
Permissions and roles. To create or modify masking rules, you must have the ALTER ANY MASK and ALTER permissions on the table. End-users who only have SELECT permissions on the table or view will automatically be served masked data if they do not have UNMASK permission.
Assessment of sensitive fields. Identify which columns contain PII or sensitive data. Typical candidates:

Email addresses
Phone numbers
National identifiers (e.g., SSN)
Credit card numbers
Passwords or security answers

How to Implement Dynamic Data Masking

1. Identify Columns to Mask

 Review each column and decide which requires masking using the query below: