First published on MSDN on May 07, 2018
Some days ago, I have been working on an issue that our customer is trying to enable Data Masking for a specific column of a table and they got an error "the index ... is dependent on column ..."
Working on this issue, we found that this issue correspond when you have the following situation trying to enable Data Masking for a specific column of a table:
-
You have a table with a filtered index.
-
The filtered index is using the column that you want to enable Data Masking
The solution to be able to do it:
-
Remove the index
-
Apply the formula for Data Masking
-
Create the index again.
This is an explanation about the issue
URL
:
“Adding a dynamic data mask is implemented as a schema change on the underlying table, and therefor cannot be performed on a column with dependencies. To work around this restriction, you can first remove the dependency, then add the dynamic data mask and then re-create the dependency. For example, if the dependency is due to an index dependent on that column, you can drop the index, then add the mask, and then re-create the dependent index.”
This is the script to reproduce the issue:
CREATE TABLE Contacto3
(ID int IDENTITY,
Nombre varchar(100) NOT nULL,
Apellido varchar(100) NOT NULL,
NrTlf varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
CONSTRAINT [PK_Contacto3] PRIMARY KEY CLUSTERED
(
id ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20180428-10221223] ON [dbo].[Contacto3]
(
[Nombre] ASC
)
WHERE Nombre='Roberto'
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
INSERT Contacto3 (Nombre, Apellido, NrTlf, Email) VALUES
('Roberto', 'Torres', '91551234567', 'RTorres@contoso.com'),
('Juan', 'Galvin', '95551234568', 'JGalvin@contoso.com'),
('José', 'Garcia', '95551234569', 'Jgarcia@contoso.net');
ALTER TABLE Contacto3
ALTER COLUMN Nombre ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')
Enjoy!
