You might have already noticed the first DDM limitation. The UNMASK permission currently works at the database level only. You also cannot mask 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 masked values.

Carefully crafted queries can also bypass DDM. Some numeric system functions automatically unmask 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 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; 

If you execute the preceding code in SQL Server 2016, you get the following results:

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

This problem is mitigated in SQL Server 2017. If you execute the same code in SQL Server 2017, you get the following results:

SalaryMaskedRandom  SalaryExpLog  SalarySqrtSquare
------------------  ------------  ----------------
70618               0             0

Filtering in a query also works on the unmasked value. This issue is present in both, SQL Server 2016 and 2017. For example, the SalesUser2 user can check which employees have a salary greater than 50000 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