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: bit, tinyint, smallint, int, float & money.

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='bit' ORDER BY T.NAME,C.NAME

Bit Data type

Let’s go with the bit Data Type 😛
bit Data Type masking
I select one of these columns, for example: DimEmployee.SalesPersonFlag
With this Data Type, only the default masking mode works

ALTER TABLE [DimEmployee] ALTER COLUMN [SalesPersonFlag] ADD MASKED WITH (FUNCTION = 'default()')

And the question is, what the user u2 see in place of the real data…
Dynamic Data Masking default masking mode for Bit data type
I test with u2 and I’m surprise. Data are masked! Cool I have only ‘0’ for all data. 😉

Tinyint Data type

I select the column and type with this data type and I select one of this column for my test and use only the default masking mode:
Query for tinyint Data type

With u1, I see all different values and 18484 rows
without masking mode
With u2, I see for all 18484 rows but with a value 0.
with masking mode

Smallint & int Data types

I do the same for these others “integer” data types:

    • smallint Data type selection and masking:

Dynamic Data Masking default masking mode for smallint Data type

    • int Data type selection and masking:

Dynamic Data Masking default masking mode for int Data type

With u1, I see all different values.
smallint & int Data Type without masking
With u2, I see for all rows with a value of 0.
smallint & int Data Type with masking

Float Data Type

Like all integers data types, the Dynamic Data Masking replace the value per 0 with the default masking mode.
Dynamic Data Masking default masking mode for Float

With u1, I see all different values.
Float Data Type without masking
With u2, I see for all rows with a value of 0.
Float Data Type with masking

Money Data Type

Like all numerical data types, the Dynamic Data Masking replace the value per 0 with the default masking mode.
Dynamic Data Masking default masking mode for Money

With u1, I see all different values.
Money Data Type without masking
With u2, I see for all rows with a value of 0 and NULL.
Money Data Type with masking

It is good to notice that a NULL value is not masked.

Core message

  • Numeric Data Types tested use only the default masking mode
  • The default value is 0
  • A NULL value is not masked