Using aggregate function on data masked column returns zeroes
1
vote
1
answer
725
views
Recently we have implemented dynamic data masking in SQL Server 2019 database in order to hide sensitive information from developers. However, for testing purposes I'd like them to see close-to-real values, so my mask looks like this:
CREATE TABLE fin.Salaries
(
TargetMonth DATE NOT NULL,
Login VARCHAR(100) NOT NULL,
Department VARCHAR(100) NOT NULL,
AmountUSD MONEY MASKED WITH (FUNCTION = 'random(500, 5000)') NOT NULL,
CONSTRAINT PK_Salaries PRIMARY KEY CLUSTERED (TargetMonth, Login, Department)
)
It works fine for simple queries, however aggregate functions seem to always return zero values. I haven't found anything on this in the documentation, so the question is whether this is an intended behavior and is there any way to get non-zero values from aggregated data masked column?
SELECT TargetMonth,
AVG(AmountUSD) -- Returns all zeroes
FROM fin.Salaries
GROUP BY TargetMonth
Asked by Sergey Rybalkin
(165 rep)
Jun 26, 2020, 03:13 PM
Last activity: Jun 26, 2020, 07:09 PM
Last activity: Jun 26, 2020, 07:09 PM