Problem with unmasking a database user during a sproc I am having an issue with a stored procedure that refreshes dynamic data masking on a database. This sproc is run as a job against all databases on this server.
The problem that I have is that I have a system database user that handles API calls to the application that these DBs are a part of. We'll call the user &quot;apiuser&quot;. The user needs to be unmasked in order to function.
Not every database has this user for various reasons (API is not enabled on all databases by default), so when the sproc runs, it should attempt to unmask the user only if they exist on that DB. What I came up with to do that was this:
if exists (select name from [sys].[database_principals] where name=N'apiuser')
GRANT UNMASK TO apiuser;

This line works fine when run on its own as a statement. However, when run as part of a stored procedure, it does not work. There is no error, the sproc runs just fine, it just does not unmask the user. My guess is for whatever reason the sproc is not handling the if exists part right, and so skips this component.
SET NOCOUNT ON; 
GRANT UNMASK TO systemuser1; 
GRANT UNMASK TO systemuser2; 
if exists (select name from sys.database_principals where name='ApiUser') 
  EXEC('GRANT UNMASK TO apiuser'); 
ALTER TABLE Table1 ALTER COLUMN Field1 ADD MASKED WITH (FUNCTION = 'default()'); 
ALTER TABLE Table2 ALTER COLUMN Field1 ADD MASKED WITH (FUNCTION = 'default()'); 
ALTER TABLE Table2 ALTER COLUMN Field2 ADD MASKED WITH (FUNCTION = 'default()'); 
END

Worth noting the other two unmasks (the other system users) work just fine. It's only the one using the IF EXISTS that does not.
To be honest, I've never used SQL Server masking before so this is mostly a guess, but if you try to run just the line of code EXEC('GRANT UNMASK TO apiuser'); directly, does it work?
I'm thinking by executing dynamic SQL you're running into the same limitation as if you created a temp table with dynamic SQL and then tried to access that table outside of the dynamic SQL after (it's not possible).
I can't find exactly why that's how SQL Server works with dynamic SQL, but here's some relevant posts (regarding temp tables) and I bet you're experiencing the same thing with unmask:
SQL Authority: SQL Server Dynamic SQL and Temporary Tables
StackOverflow: T-SQL Dynamic SQL and Temp Tables
As a solution, can you run just the GRANT UNMASK directly (eliminate the dynamic SQL) after the IF instead like your first example?
if exists (select name from [sys].[database_principals] where name=N'apiuser')
GRANT UNMASK TO apiuser;