Date column masking and format change

Question

Hello,

I have a datetime column with dynamic data masking applied on it using function ='default()'.

When apply some date format on this masked datetime column in SELECT , it always returns as xxxx, not in a date format. Is this the exact behavior?

eg. CONVERT(VARCHAR,P.DOB,23) -- xxxx

Thanks,
Ashru

Answer 1

I explored this a little more, and I think I understand what happens: the masking is applied after the conversion. Thus, since it is a string at this point, you get xxxx.

I will also submit that this is what you want. Consider:

   CREATE USER stisse WITHOUT LOGIN  
   CREATE TABLE Orvar (d varchar(20) MASKED WITH (FUNCTION = 'default()'))  
     
   INSERT Orvar(d) VALUES('20220125')  
     
   GRANT SELECT ON Orvar TO stisse  
   go  
   EXECUTE AS USER = 'stisse'  
   go  
   SELECT d, convert(datetime, d, 23)  FROM Orvar  
   go  
   REVERT  
   go  
   DROP USER stisse  
   DROP TABLE Orvar  

This is the output:

![![212622-image.png][1]][1]

Now, imagine that masking had occurred at the source. In that case, the conversion to datetime would have failed, since xxxx cannot be converted to datetime.

Answer 2

For Li who did not understand the question, here is a demo:

   CREATE TABLE Orvar (d datetime MASKED WITH (FUNCTION = 'default()'))  
     
   INSERT Orvar(d) VALUES(sysdatetime())  
     
   GRANT SELECT ON Orvar TO public  
   go  
   EXECUTE AS USER = 'guest'  
   go  
   SELECT d, convert(varchar(23), d, 23)  FROM Orvar  
   go  
   REVERT  

I have not dug into Dynamic Data Masking with that much detail, so the only answer I can give to Ashru's question is: Apparently.

Answer 3

That is because you are converting it to a string.

For string data types, use XXXX or fewer Xs if the size of the field is fewer than 4 characters (char, nchar, varchar, nvarchar, text, ntext).

See:
https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16#defining-a-dynamic-data-mask

Answer 4

ErlandSommarskog's comment exactly describes my case. But see below to understand what exactly it returns at my end.

SELECT d, convert(varchar(23), d, 23) FROM Orvar -- Output: 1900-01-01, "xxxx" (Exactly 4 Xs)

Answer 5

Thank you ErlandSommarskog. Yes the masking applies after conversion.

In my case the column datatype should be datetime of type. I tested the modifying it to datetime datatype, and when SELECT data I converted to date related datatypes, as below.

SELECT convert(date, d2, 23) -- Used date datatype to remove time.