• Grey Twitter Icon
  • Grey Facebook Icon
  • Grey LinkedIn Icon
  • Grey Google+ Icon

© 2016 - 2019 All rights reserved. PigeonSQL.com | Filip Holub

How to configure Dynamic Data Masking?

January 8, 2019

Microsoft introduced with SQL Server 2016 new feature - Dynamic Data Masking. This feature can be useful if you don't want someone to see sensitive data. Let's say that your company hires DBA contractor for 3 months, but IT Security guy doesn't want to he couldn't see a number of credit cards of our customer. For this purpose, we can use this masking function, be aware that DDM don't use encryption method but masking method.



Dynamic Data Masking supports four types of mask:


1 ) Default - The masking depends on data types.

  • Binary data types - binary, varbinary and image

  • Numeric data types - int, money, numeric, float, real and etc...

  • Time and date data types - date, datetime2, datetimeoffset, , time, etc..

  • String data types - char/nchar, varchar/nvarchar, text/ntext


2) Custom string - This type show you only first and last letter and add random string in the middle.


3) Random - This method masks random value within specified range of numeric type.


4) Email - It allows display for example first letter, at(@) and .com, such as hXXXX@XXXX.com


DDM doesn't support following:

  • Always Encrypted columns

  • Filestream data

  • Computed columns

  • You cannot FULLTEXT index where DDM is implemented.

  • Etc...


Implementation of Dynamic Data Masking via T-SQL looks like this.


 Now look at Email column before and after configure DDM.

As you can see for non-privileged user named Pigeon are data masked in Email column!


For more info visit official documentation on MSDN.


See you later Alligator.




Please reload

Please reload

  • Facebook Social Icon
  • Google+ Social Icon
  • LinkedIn Social Icon
  • Twitter Social Icon