Want to selectively hide or scramble sensitive data? In this video, I'll show you how to use a new feature/tool in SQL Server 2016, called Dynamic Data Masking, that allows you to partly obfuscate certain columns of data in a table or view.

--
Hey, if you enjoyed this video, please SUBSCRIBE to HandyAndy Tech Tips!
--

My notes on this topic:
- Now, to add the masking, you COULD define the masking in the table definition statement, but I'll use the ALTER TABLE statement. Anyway, the basic syntax is the same: MASKED WITH (FUNCTION = '').

The string can basically be one of four functions.

- The easiest is default() - everything is automatic. It:
---- on a TEXT FIELD, replaces the original value of the field with 4 X's.
---- on a DATE FIELD, set s the date to 1900-01-01 00:00:00.00
---- on a NUMERIC FIELD, sets the value to 0.

- email() only shows the first letter, and replaces the remainder of the field with X's, an @ symbol, and a standard .com suffix. It, of course, also works on fields that don't include actual email addresses - so it therefore works on any VARCHAR or CHAR.

- partial() - When used as partial(no-of-chars-to-reveal-before, padding, chars-to-reveal-after), it reveals the first character and the last two, separated by a string of X's. This also only works on strings - not on numeric types or dates.

- random(lower-bound, upper-bound) replaces the field's original value with a randomly-generated number between 1 and 10. This, of course, only works with numeric data types like INT.

- Now, it's worth noting that if you want to give a particular user - or role - the ability to see all of the data in its unmasked form, then you can simply grant them the UNMASK privilege. Annoyingly, this doesn't seem to work on a table level, only for the entire database.

свернуть