You define DDM at the column level. You can obfuscate values from a column in a table by using four different masking functions:

You must give the users the UNMASK database level permission if you want them to see unmasked data.

Let's start testing the DDM feature by creating a new demo database and changing the context to the newly created database:

USE master; 
IF DB_ID(N'DDMDemo') IS NULL 
CREATE DATABASE DDMDemo; 
GO 
USE DDMDemo; 

Next, you need a couple of database users for the test:

CREATE USER SalesUser1 WITHOUT LOGIN; 
CREATE USER SalesUser2 WITHOUT LOGIN; 

The following code creates and populates a demo table using the SELECT INTO statement. It uses the employees from the WideWorldImporters demo database, and adds a randomized salary.

SELECT PersonID, FullName, EmailAddress, 
 CAST(JSON_VALUE(CustomFields, '$.HireDate') AS DATE) 
  AS HireDate, 
 CAST(RAND(CHECKSUM(NEWID()) % 100000 + PersonID) * 50000 AS INT) + 20000 
  AS Salary 
INTO dbo.Employees 
FROM WideWorldImporters.Application.People 
WHERE IsEmployee = 1; 

You must grant the SELECT permission on this table to the two database users:

GRANT SELECT ON dbo.Employees 
TO SalesUser1, SalesUser2; 

If you execute the following queries, you can see that you, as the dbo user, and both database users you created, can see all of the data:

SELECT * FROM dbo.Employees; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser1'; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser2'; 

Here is the partial result of one of the three previous queries:

PersonID FullName       EmailAddress                   HireDate   Salary
-------- -------------  ------------------------------ ---------- ------
2        Kayla Woodcock [email protected]  2008-04-19 45823
3        Hudson Onslow  [email protected] 2012-03-05 39344

The following code adds masking:

ALTER TABLE dbo.Employees ALTER COLUMN EmailAddress 
  ADD MASKED WITH (FUNCTION = 'email()'); 
ALTER TABLE dbo.Employees ALTER COLUMN HireDate 
  ADD MASKED WITH (FUNCTION = 'default()'); 
ALTER TABLE dbo.Employees ALTER COLUMN FullName 
  ADD MASKED WITH (FUNCTION = 'partial(1, "&&&&&", 3)'); 
ALTER TABLE dbo.Employees ALTER COLUMN Salary 
  ADD MASKED WITH (FUNCTION = 'random(1, 100000)'); 
GO 

Try to read the data as one of the regular users:

EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser1'; 

The result for this user is masked:

PersonID  FullName        EmailAddress                 HireDate    Salary
--------  --------------  ---------------------------  ----------  ------
2         K&&&&&ock       [email protected]                1900-01-01  57709
3         H&&&&&low       [email protected]                1900-01-01  44627

Note that you might get different values for the salary because this column uses the random masking function. Now you can grant the UNMASK permission to the SalesUser1 user, and try to read the data again. This time, the result is unmasked.

GRANT UNMASK TO SalesUser1; 
EXECUTE (N'SELECT * FROM dbo.Employees') AS USER = N'SalesUser1'; 

You might have already noticed the first DDM limitation. The UNMASK permission currently works at the database level only. You also cannot mask the columns encrypted with the AE feature. FILESTREAM and COLUMN_SET (sparse) columns don't support masking either. A masked column cannot be used in a full-text index. You cannot define a mask on a computed column. If a user who does not have permission to unmask the columns creates a copy of the data with the SELECT INTO statements, then the data in the destination is converted to masked values and the original data is lost. For example, the following code gives the CREATE TABLE and ALTER SCHEMA permissions to both test users, while only the first user has the UNMASK permission. Both users execute the SELECT INTO statement.

GRANT CREATE TABLE TO SalesUser1, SalesUser2; 
GRANT ALTER ON SCHEMA::dbo TO  SalesUser1, SalesUser2; 
EXECUTE (N'SELECT * INTO dbo.SU1 FROM dbo.Employees') AS USER = N'SalesUser1'; 
EXECUTE (N'SELECT * INTO dbo.SU2 FROM dbo.Employees') AS USER = N'SalesUser2'; 
GO 

You can query the two new tables as the dbo user. The values in the table created by the SalesUser2 user are converted into the masked values.

Carefully crafted queries can also bypass DDM. Some numeric system functions automatically unmask the data in order to perform the calculation. The following query is executed in the context of the SalesUser2 user, who does not have permission to unmask the data.

EXECUTE AS USER = 'SalesUser2'; 
SELECT Salary AS SalaryMaskedRandom, 
 EXP(LOG(Salary)) AS SalaryExpLog,  
 SQRT(SQUARE(salary)) AS SalarySqrtSquare 
FROM dbo.Employees 
WHERE PersonID = 2; 
REVERT; 

Here are the results:

SalaryMaskedRandom  SalaryExpLog  SalarySqrtSquare
------------------  ------------  ----------------
70618               45822.96875   45823

Filtering in a query also works on the unmasked value. For example, the SalesUser2 user can check which employees have a salary greater than 50,000 with the following query:

EXECUTE AS USER = 'SalesUser2'; 
SELECT * 
FROM dbo.Employees 
WHERE Salary > 50000; 
REVERT; 

Here are the abbreviated results.

PersonID  FullName   EmailAddress   HireDate    Salary
--------  ---------  -------------  ----------  ------
4         I&&&&&upp  [email protected]  1900-01-01  8347
8         A&&&&&sse  [email protected]  1900-01-01  60993

Please note that you might get different results because the Salary column is masked with the random masking function. Finally, you can clean up your SQL Server instance:

USE master; 
DROP DATABASE DDMDemo; 
GO