I'm trying to generate XML from a database and using the Dynamic Data Masking for hiding sensitive information but when I execute my stored procedures that generate the XML as the user who should only see the masked data, the finished XML lacks the output from subqueries and only generates <masked /> instead of the usual tag and, in this case, masked contents of that tag. When I run the stored procedures as my regular db user I get the XML-results I need.
I've tried changing the rights for my "masked db user" but I can't manage to resolve the above issue.
This is the content of my stored procedure: WITH XMLNAMESPACES (DEFAULT 'urn:svsys:export:user') SELECT u.userId ,u.identityNumber ,u.firstName ,u.lastName ,(SELECT ur.role FROM dbo.UserRole ur WHERE ur.userId = u.id FOR XML PATH (''), ROOT ('userRoles'), TYPE, ELEMENTS) FROM dbo.[User] u INNER JOIN dbo.LegalCareUnit lcu ON u.legalCareUnitId = lcu.id WHERE lcu.legalCareUnitId = @LegalCareUnitId FOR XML PATH ('user'), ROOT ('users')
and an example of output when executed by my regular db user, ie. without Dynamic Data Masking:
<users xmlns="urn:svsys:export:user"> <user> <userId>2</userId> <identityNumber>111</identityNumber> <firstName>Paddy</firstName> <lastName>Smith</lastName> <userRoles xmlns="urn:svsys:export:user"> <role>testRole</role> </userRoles> </user> </users>
and how the XML looks when executing as my masked db user (with Dynamic Data Masking):
<users xmlns="urn:svsys:export:user"> <user> <userId>2</userId> <identityNumber>xxx</identityNumber> <firstName>Paxxx</firstName> <lastName>Smxxx</lastName> <masked xmlns="" /> </user> </users>
what I would like to get when executing as my masked db user (with Dynamic Data Masking): <users xmlns="urn:svsys:export:user"> <user> <userId>2</userId> <identityNumber>xxx</identityNumber> <firstName>Paxxx</firstName> <lastName>Smxxx</lastName> <userRoles xmlns="urn:svsys:export:user"> <role>texxxxxx</role> </userRoles> </user> </users>
As you can see the tag in my first example <userRoles xmlns="urn:svsys:export:user"> is replaced by <masked xmlns="" /> in the second example.
Any idea how I can get the tag <userRoles> with masked information inside the child tag <role>, as in my last example?