Dynamic Data Masking in SQL Server 2016 and Later

• 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 that exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
• 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 SQL Server Dynamic Data Masking Demo

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
BEGIN
CREATE DATABASE SampleDB_DataMasking;
END
GO

--Use sample database
USE SampleDB_DataMasking;
GO

--Create sample table
CREATE TABLE tblEmployeesMasking
    (
      empName VARCHAR(250) ,
      empPhoneNo VARCHAR(20) ,
      empEmailAddress VARCHAR(100)
    );
GO

--Populate sample table with data
INSERT  INTO dbo.tblEmployeesMasking
VALUES  ( 'John Clarck', '545-5478-1234', 'john.clark@example.com' ),
        ( 'Kostas Andreou', '878-8888-5678', 'kostas.andreou@example.com' ),
        ( 'Wei Xing', '673-5123-9101', 'wei.xing@example.com' );

--Check records (unmasked)
SELECT * FROM tblEmployeesMasking

Output 1:

--Now let's mask the email address
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empEmailAddress VARCHAR(100) MASKED WITH (FUNCTION = 'email()');

Let's check again the records of the table, this time using a non-sysadmin user.

--Create user (non-sysadmin) with GRANT SELECT permission on the sample table
CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON dbo.tblEmployeesMasking TO TestUser;
GO

--Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output 2:

REVERT;
GO

--Let's mask the phone number with default
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empPhoneNo VARCHAR(100) MASKED WITH (FUNCTION = 'default()');

Let's check the records of the table using a non-sysadmin user.

--Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = 'TestUser';
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output 3:

Discussion

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.

References:

• MSDN Library Article: What’s New in SQL Server 2016
• MSDN Library Article: Dynamic Data Masking
• Microsoft Azure Document: Get started with SQL Database Dynamic Data Masking

See also…