Did you say Data Masking?

Author: Eric Rouach, Madeira Data Solutions

Date: August 2022



If you are using SQL Server and need a lightweight encryption solution at the database user resolution with almost no changes to your application, then Dynamic Data Masking might definitely be for you!


DDM allows you to prevent unprivileged users from viewing secret data such as password, credit card numbers or any data you might consider sensitive.


It has been available since SQL Server 2016 and is also available for Azure SQL Database and Azure SQL Managed Instance. Some additional features are only available for preview on SQL Server 2022 by the time of this article.


DDM uses built-in functions applied on a column containing the sensitive data. It is very user friendly and easily removable. You should also note that DDM will not prevent highly privileged users from viewing the masked data so that it is not a substitute to Always Encrypted along with RBAC (Role Based Access Control).


Let's go through a few examples:


For the following demo, I have used the AdventureWorks2014 database.

You may copy and paste the following T-SQL code into SSMS and try it yourself!


USE [AdventureWorks2014];

GO


SELECT

*

FROM

Sales.CreditCard


Here's a sample of the result set:


--Create a new SQL Server login

CREATE LOGIN DDM_User1 WITH PASSWORD = 'StrongPassword123!';

GO


--Create a database user mapped to the previously created login

CREATE USER DDM_User1 FOR LOGIN DDM_User1


--Grant SELECT permissions to DDM_User1