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

• The default function implements full masking. The mask depends on the data type of the column. A string is masked by changing each character of a string to X. Numeric values are masked to zero. Date and time data type values are masked to 01.01.2000 00:00:00.0000000 (without double quotes). Binary data is masked to a single byte of ASCII value 0.
• The email function masks strings that represent e-mail addresses in the form: [email protected].
• The random function masks numeric values to a random value in a specified range.
• The partial function uses a custom string to mask character data. You can skip masking some characters at the beginning of the string (prefix) or at the end of the string (suffix).

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, with the help of the following code:

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 to the previous queries:

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 

Now we will try to read the data as one of the regular users, using the following code:

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

The result for this user is masked, as shown here:

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';