Sample Header Ad - 728x90

Problem with unmasking a database user during a sproc

1 vote
2 answers
402 views
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 "apiuser". 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.
Asked by Tom H (11 rep)
Oct 21, 2020, 02:37 PM
Last activity: Mar 3, 2025, 12:59 PM