SQL Server 2016 – Security: Dynamic Data masking – String Data Types

This blog is a part of the series “SQL Server 2016 – Security: Dynamic Data Masking“.
I remember you my different logins used to connect to AdventureWorks:

• sql login u1 with the db_owner role –> No masking
• sql login u2 with the db_datareader role –> masking

The Data Types used are: sysname, char, nchar, nvarchar and varchar.

I run this query to have all columns with the tested data type in the clause WHERE:

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE]
FROM SYS.OBJECTS AS T JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC='USER_TABLE' and T.NAME<>'sysdiagrams' and P.NAME='sysname' ORDER BY T.NAME,C.NAME

Sysname Data Type

With Sysname Data Type, I test all masking mode:

• Default
• Partial(1,”XXXXXX”,0) –> means that exposes the first and last letters and adds a custom padding string in the middle.
• Email

With u1, I see all different values without masking.

With u2, I see for all rows with:

In the result, you can see that default mode masks with ‘xxxx’, the partial mode masks with the first letter and the 6 ‘X’ that I configure and the email mode masks with the first letter and an email format.

Char and nchar Data Types

Like for Sysname, I test all masking modes

With u1, I see all different values without masking:

With u2, I see for all rows with:

In the result, you can see that default mode masks with one ‘X’, the partial mode masks with the first letter and one ‘X’ and the email mode masks with the first letter and no email format but one ‘X’ like partial.

Varchar and nvarchar Data Types

I test also all masking modes

With u1, I see all different values without masking.

With u2, I see all rows with:

In the result, you can see that is like sysname (its logic! ;-))

Core Message

For Sysname, varchar and nvarchar data types:

• the default masking mode is enabled and masks with ‘xxxx’
• the partial masking mode is enabled and masks with the expression: prefix,[padding],suffix
• the email masking mode masks is enabled and with the first letter and an email format

For char and nchar data types:

• the default mode is enabled and masks with one ‘X’
• the partial mode is enabled but masks with the first letter and one ‘X’
• the email mode is enabled but  masks with the first letter and one ‘X’ but not an email format