If I have a SQL Server user that has UNMASK permissions on a table, is it possible for that user to still get results with masked data?
My use case is that I have a service that returns employee data. Since SQL Server does not support OAuth2 / ODIC, we use a single SQL Server User to connect to the database. We give that user UNMASK permissions.
The actual OAuth2 user may or may not have permissions to see the masked columns. If they do not, I would like to return the data to them masked. If they do, then I would like to return the data to them unmasked.
I guess I could make two SQL Server Users. One with UNMASK and one without. Then, depending on the OAuth2 user's permissions, pick the correct user. But I would rather not have to manage two separate connection types to my database. (It seems like it might cause issues with caching and such.)
Hence my question: Can a user with UNMASK permissions execute a query in such a way as to get masked results?