- Default: Full masking according to the data types of the designated fields.
- Email: Masking method which exposes the first letter of an email address and the constant suffix “.com”, in the form of an email address. . aXXX@XXXX.com.
- Custom String: Masking method which exposes the first letter of an email address and the constant suffix “.com”, in the form of an email address. . aXXX@XXXX.com.
- Random: A random masking function for use on any numeric type to mask the original value with a random value within a specified range.
A simple example of Dynamic Data Masking is to display only the last 4 digits of a phone number to the support personnel of an IT Department or don’t show the email address to unauthorized users, etc.
Now let’s proceed with a demo in order to see some examples of using Dynamic Data Masking.
Demo: Dynamic Data Masking
–Create a sample database
IF DB_ID(‘SampleDB_DataMasking’) IS NULL
CREATE DATABASE SampleDB_DataMasking;
–Use sample database
–Create sample table
CREATE TABLE tblEmployeesMasking
empName VARCHAR(250) ,
empPhoneNo VARCHAR(20) ,
–Populate sample table with data
INSERT INTO dbo.tblEmployeesMasking
VALUES ( ‘John Clarck’, ‘545-5478-1234’, ‘firstname.lastname@example.org’ ),
( ‘Kostas Andreou’, ‘878-8888-5678’, ‘email@example.com’ ),
( ‘Wei Xing’, ‘673-5123-9101’, ‘firstname.lastname@example.org’ );
–Check records (unmasked)
SELECT * FROM tblEmployeesMasking
Dynamic Data Masking is a significant feature in SQL Server 2016 and Azure SQL Database. As you can see from the above examples it is very easy to use it and it can really help you when it comes to Security and Compliance regarding yours or your organization’s most valuable asset: the data.
MSDN Library Article: What’s New in SQL Server 2016 Release Candidate (RC2)
MSDN Library Article: Dynamic Data Masking
Microsoft Azure Document: Get started with SQL Database Dynamic Data Masking (Azure Portal)
Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)
SQL Server 2016 Top Features
My Latest Projects:
- DBA Security Advisor: Secure your SQL Server instances against security risks.
- In-Memory OLTP Simulator: Easily benchmark SQL Server’s In-Memory OLTP Engine against your custom workload.
- Artemiou SQL Books: Download my latest free books on SQL Server.
- Artemiou Data Tools: See my latest software projects.
Recommended eBooks on SQL Server: