I am trying to create an index on a view that references a table with a masked column (SQL Server 2016). The masked column is not the only one in that table, and it's not used in the view.
create unique clustered index [IX_Name]
on dbo.vw_ViewName(SomeUniqueId)
I get this error:
The index on view 'dbo.vw_ViewName' cannot be created
because the view is referencing table 'dbo.TableName' with masked
columns.
In another environment, where masking is not enabled, the index is created successfully.
I looked through about four pages of Google results and haven't found any sensible description of the error. I would appreciate any knowledge transfer on the error and why it is not possible to create such index.
Here is some SQL to reproduce the issue:
drop view if exists dbo.vw_Aggregate
drop table if exists dbo.MainTable, dbo.SecondaryTable
go
create table dbo.MainTable
(
MainTableId uniqueidentifier primary key,
SomeExternalId uniqueidentifier,
SecondaryTableId uniqueidentifier
)
go
create table dbo.SecondaryTable
(
SecondaryTableId uniqueidentifier primary key,
CreatedOn datetime,
Amount decimal(19, 8),
-- the below column produces error,
-- if commented out - there is no error
[Description] nvarchar(max) masked with (function = 'default()'),
Dummy int
)
go
create view dbo.vw_Aggregate with schemabinding
as
select AggregateId = m.MainTableId,
m.SomeExternalId,
s.CreatedOn,
s.Amount
from dbo.MainTable m
inner join dbo.SecondaryTable s on s.SecondaryTableId = m.SecondaryTableId
go
create unique clustered index [IX_dbo.vw_Aggregate(AggregateId)]
on dbo.vw_Aggregate(AggregateId)
go
Best Answer
As far as I know, this is not supported, and you can only use tables in this case.
As found in following article which is using Row level security which is not the same as Dynamic Data masking, but it might be linked to each other.
https://www.mssqltips.com/sqlservertip/4005/sql-server-2016-row-level-security-limitations-performance-and-troubleshooting/ by Aaron Bertrand:
Indexed Views
Row-Level Security is not compatible with tables that have indexed views. If you try to create an indexed view against a table with a security policy, you will get the following error:
And no, you can't chicken-and-egg this to work around it; if a table is referenced by an indexed view, a security policy cannot be applied:
Note: You may come across the same types of problems if trying to apply a security policy to a table that is reference by a partitioned view.
As Dynamic Data Masking & Row Level Security were released in the same release maybe the limitations are linked to each other.
The Error
The error also clearly says that it is not allowed to have an index on a view linked to a table with a masked column. In the documentation however nothing can be found about it.
So my guess is that it is just not supported.