2

Here is the code to reproduce the issue:

CREATE TABLE [dbo].[EmployeeDataMasking](
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED 
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO

Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

-- Partial data masking works correctly here
Select  
    EmployeeId,
    LastName,
    FirstName
From dbo.EmployeeDataMasking

-- Partial data masking does not work correctly here. Full masking is used.
Select  
    EmployeeId,
    ISNULL(LastName,'') as LastName,
    ISNULL(FirstName,'') as FirstName
From dbo.EmployeeDataMasking

enter image description here

It appears, when using ISNULL, the partial masking is being converted to a full (default) mask. Is this the expected behavior?

Update: I discovered that COALESCE does not exhibit this problem. These queries return the expected results:

Select  
    EmployeeId,
    COALESCE(LastName,'') as LastName,
    COALESCE(FirstName,'') as FirstName
From dbo.EmployeeDataMasking

Using Azure SQL Database

2

As a test, I created a custom user function that does nothing except pass the string back to the caller; it also exhibits this behavior.

Be aware, this code will drop users and objects, so only run it in a test database.

First, we create the required objects, users, and insert some data:

IF OBJECT_ID(N'dbo.EmployeeDataMasking', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.EmployeeDataMasking;
END
IF OBJECT_ID(N'dbo.SomeFunc', N'FN') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.SomeFunc;
END
GO

IF EXISTS (
    SELECT 1
    FROM sys.database_principals dp
    WHERE dp.name = N'SomeUser'
    )
BEGIN
    DROP USER [SomeUser];
END
CREATE USER [SomeUser] WITHOUT LOGIN;
ALTER ROLE db_datareader ADD MEMBER [SomeUser];

CREATE TABLE [dbo].[EmployeeDataMasking](
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeId] [int] NULL,
    [LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
    [FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
 CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED 
(
    [RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO

GO
CREATE FUNCTION dbo.SomeFunc(@i varchar(50))
RETURNS varchar(50)
AS
BEGIN
    RETURN @i;
END
GO
ALTER AUTHORIZATION ON dbo.SomeFunc TO [SOmeUser];
GRANT EXECUTE TO [SomeUser];
GRANT SHOWPLAN TO [SomeUser];


Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')

Next, we'll query the data from the perspective of the non-privileged user:

EXECUTE AS USER = N'SomeUser';
Select  
    EmployeeId,
    ISNULL(LastName,'') as LastName,
    ISNULL(FirstName,'') as FirstName,
    dbo.SomeFunc(LastName) as LastName,
    dbo.SomeFunc(FirstName) as FirstName,
    COALESCE(LastName,'') as LastName,
    COALESCE(FirstName,'') as FirstName
FROM dbo.EmployeeDataMasking
REVERT

The results:

╔════════════╦══════════╦═══════════╦══════════╦═══════════╦══════════╦═══════════╗
║ EmployeeId ║ LastName ║ FirstName ║ LastName ║ FirstName ║ LastName ║ FirstName ║
╠════════════╬══════════╬═══════════╬══════════╬═══════════╬══════════╬═══════════╣
║          1 ║ xxxx     ║ xxxx      ║ xxxx     ║ xxxx      ║ SmXXXXan ║ DaXXXXel  ║
║          2 ║ xxxx     ║ xxxx      ║ xxxx     ║ xxxx      ║ TeXXXXon ║ RoXXXXld  ║
╚════════════╩══════════╩═══════════╩══════════╩═══════════╩══════════╩═══════════╝

As you can see, the custom user function, dbo.SomeFunc exhibits the same behavior as ISNULL. At this point, I'm unsure why, since COALESCE does not exhibit the unwanted behavior.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.