SQL Server help

Thursday, January 15, 2026

SQL Server Random Masking: Anonymizing Sensitive Numeric Data with DDM

Summary: Learn how to use the random() masking function in SQL Server Dynamic Data Masking. This tutorial explains how to replace sensitive numeric values like salaries and IDs with random data within a custom range.

Implementing Random Masking in SQL Server

The random() function is a powerful component of SQL Server's Dynamic Data Masking (DDM) suite. Unlike string-based masks, Random Masking is designed specifically for numeric data types, allowing you to present "fake" but realistic numeric values to unauthorized users.

What is Random Masking?

The random(start, end) function replaces the actual numeric value in a column with a random value chosen from the specified range. This is ideal for masking sensitive financial data, scores, or identifiers while maintaining the numeric integrity of the application.

Function Syntax

MASKED WITH (FUNCTION = 'random([start_range], [end_range])')

T-SQL Example: Masking Salaries and Bonus Points

In this example, we apply masks to a Salary column (Money) and an InternalScore column (Int).


-- Create a new table with Random Masking
CREATE TABLE EmployeePayroll (
    EmpID INT IDENTITY(1,1) PRIMARY KEY,
    EmpName VARCHAR(100) NOT NULL,
    -- Masking Salary to show a random value between 30k and 50k
    Salary MONEY MASKED WITH (FUNCTION = 'random(30000, 50000)'),
    -- Masking Score to show a random number between 1 and 10
    PerformanceScore INT MASKED WITH (FUNCTION = 'random(1, 10)')
);

-- Insert real data
INSERT INTO EmployeePayroll (EmpName, Salary, PerformanceScore)
VALUES ('John Doe', 95000, 9), ('Jane Smith', 110000, 10);

-- Query as a masked user
SELECT * FROM EmployeePayroll;

Expected Output for Masked Users

Even though John Doe earns 95,000, a user without UNMASK permissions will see something like this:

EmpID	EmpName	Salary	PerformanceScore
1	John Doe	42384.12	4
2	Jane Smith	31205.50	7

Important Considerations

Supported Types: Works on bigint, int, smallint, tinyint, decimal, numeric, float, real, money, and smallmoney.
Consistency: The random value is generated at query runtime. Multiple executions of the same query may result in different random values being displayed.
Aggregations: If a masked user runs a SUM() or AVG(), the calculation is performed on the masked values, ensuring total amounts remain hidden.

Pro Tip: Use a range that mimics realistic data. If your actual salaries are 6-figures, masking them with random(1, 10) might break application logic that expects larger values!