Posted in Uncategorized

SQL SERVER – Random function in Dynamic Data Masking in SQL Server 2016 CTP 3

Hi friends,

With new CTP version of SQL Server 2016 many new functionality are added and modified. As with introduction SQL 2016 new functionality Dynamic data masking get introduced which helps in protecting the confidential data from users who do not have access to view it. In earlier CTP version data masking were of three types:

  1. Default
  2. Custom
  3. Email

I have already written about these in my earlier blogs here :

In CTP 3.0 of SQL 2016 one more type gets added with name Random.

Random function can be used for any numeric datatype to mask the value with any random number defined within a specified range.

Random ([start range], [end range])

In this blog I will give you the overview of new Random function with an example.

Let’s first create a table Demo_Masking with some data in it.

Create table Demo_Masking


EmployeeId INT,

FirstName varchar(10),

LastName varchar(20),

Salary INT



—-INSERT dummy data into Demo_masking table

INSERT INTO Demo_Masking (EmployeeId, FirstName, LastName, Salary)

SELECT 11, ‘Kapil’, ‘Singh’, 50000


SELECT 12, ‘Ankit’,’Sharma’,35000


SELECT 13, ‘Aditya’, ‘Sinha’, 45000


Here, I have created an employee with some columns, but I don’t want user to see the salary of other users so I will mask it with any random numbers.

Now, I am altering the table Demo_masking and adding masking function on columns Salary using this syntax:

ALTER TABLE Demo_Masking

ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = ‘random(1,90000)’);

After applying masking, I will create a user ‘Masking’ which will have only read access (SELECT) on table Demo_Masking.


GRANT SELECT ON Demo_Masking to Masking

Now let me execute below query –

SELECT * FROM Demo_Masking



SELECT * FROM Demo_Masking



After executing queries with current user and new user which has only SELECT rights we see that the salary column is showing random values for it and restricting the user to see the original salary of other users. In this way we can use random function to mask the data with any random numbers.

Hope you will like this post.



I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s