20 Sep’17

BIDynamic data masking in SQL SERVER

In our day to day life we are more concern about our data security and sensitive information’s when we fill out some purchase form and other billing application which requires your Credit card and other personal information. This data can be viewed easily in the backed users if it’s not secure controlled.

SQL Server 2016 provides a feature known as Dynamic Data Masking which is a security feature that limits the access of unauthorized users to sensitive data at the database layer.

As an example of the need for such a feature is allowing the applications developers to access production data for troubleshooting purposes and preventing them from accessing the sensitive data at the same time, without affecting their troubleshooting process. Another example is the call center employee who will access the customer’s information to help him in his request, but the critical financial data, such as the bank account number or the credit card full number, will be masked to that person.

Dynamic Data Masking, also known as DDM, is a simple security data protection method allows you to determine your “sensitive” data, by field in order to configure the suitable masking function to hide it from queries. This feature requires no coding effort from the application side or encrypting or applying any change to the real data stored in the disk.


  • Provides configurable masking policy using simple T-SQL command.
  • Database is not changed physically and database operations are not affected.
  • Flexibility to define a set of privileged SQL users or roles for unmasked access.

Types of Masks:

There are 4 types of masks are offered by SQL Server 2016.

1. Default Mask
The default mask masks the full data according to the assigned data type.

  • For string types (char, nchar, varchar, nvarchar, text, ntext), it replaces the data with XXXX or fewer Xs, in case size of field is less than 4.
  • For numeric datatypes (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real), it uses 0 value.
  • For binary datatypes (binary, varbinary, image), it uses a single byte of binary value 0.

2. Email Mask
The email mask especially for the fields that stores emails. It exposes only the first letter of email followed by XXX, followed by @ sign, followed by XXXX and a constant suffix “.com” to form an email. E.g. aXXX@XXXX.com

3. Custom Mask
SQL Server 2016 allows you to define your own mask for a specific field. In this method, you can define the prefix and suffix characters to be exposed, and the padding will be shown as it is. “prefix,[padding], suffix”.

4. Random Mask
Random mask can be defined over any of the numeric fields. It exposes a random number within the defined range.


1. Create a table with different mask functions

CREATE TABLE [dbo].[Contacts]
	[ID] [int] IDENTITY(1,1) NOT NULL Primary key,
	[FName] [nvarchar](30) MASKED WITH (FUNCTION = 'default()') NOT NULL,
	[LName] [nvarchar](30) NOT NULL,
	[CreditCard] [varchar](20) MASKED WITH (FUNCTION = 'partial(2, "XX-XXXX-XXXX-XX", 2)') NULL,
	[SalaryINR] [int] MASKED WITH (FUNCTION = 'default()') NULL,
	[OfficeEmail] [nvarchar](60) MASKED WITH (FUNCTION = 'email()') NULL,
	[PersonalEmail] [nvarchar](60) NULL,
	[SomeDate] [datetime] MASKED WITH (FUNCTION = 'default()') NULL

Insert Some data in the table

2.  Create a user with only select permission.

GRANT SELECT ON Contacts TO ReadOnlyUser;

3.  Now select the data with the Existing using and the new User that is created

The data will be masked for the ReadOnlyUser.


This technique allows a developer to debug the Production environment without breaching security or confidentiality for a user.

Written by

Jayant Patel

Sr. Software Developer

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Managing mailbox through Email Archiving

    20 March’ 2018

    Introduction: Email can fill your Outlook Inbox quickly – new messages, replies, and forwards. Before you know it, yo...

    Read more
  • Workaround to Report.SAVEASPDF in NAV 2018

    20 March’ 2018

    Objective: In NAV 2017 Emailing the PDF by running the report using Report.SAVEASPDF is now not allowed in NAV 2018. Th...

    Read more
  • Dynamics 365 Client Diagnostics

    15 March’ 2018

    Main cause of Performance Issues: Bandwidth and latency are the primary characteristics which affects the performance o...

    Read more
  • Closing Blind Shift in MPOS and CPOS in Dynamics 365 for Retail

    15 March’ 2018

    Manual For closing Blind shift in POS: Blind shifts terminate the currently ongoing shift and when logged in a new shif...

    Read more
  • Persistent Filters in the Power BI Service

    13 March’ 2018

    Introduction: The feature is finally release, and it is power BI has announced general availability of persistent filt...

    Read more