Insufficient user permission performing an operation on Sql Server Azure

I am trying to re-run migration on a newly created DB in azure. DB, User, and all privileges were created from scratch. I basically granted all operation permission to the user including DB admin roles for read and write: (basically tried all these)

EXEC sp_addrolemember 'db_ddladmin', N'myuser'

EXEC sp_addrolemember N'db_datareader', N'myuser'
EXEC sp_addrolemember N'db_datawriter', N'myuser'

GRANT SELECT, INSERT, UPDATE, ALTER TO myuser

But in the end I am still seeing error performing this specific operation. (logs from Azure DevOps)

2020-08-06T15:26:10.9511511Z Beginning Transaction
2020-08-06T15:26:11.0719834Z ExecuteSqlStatement ALTER TABLE [dbo].[ContextState] ALTER COLUMN [JsonContent] ADD MASKED WITH (FUNCTION = 'default()');
2020-08-06T15:26:11.2674256Z !!! An error occured executing the following sql:
2020-08-06T15:26:11.2675116Z ALTER TABLE [dbo].[ContextState] ALTER COLUMN [JsonContent] ADD MASKED WITH (FUNCTION = 'default()');
2020-08-06T15:26:11.2675549Z The error was User does not have permission to perform this action.

Answer

Not sure what extra privileges db_owner gives which was missing from what I tried above, but this fixed my problem:

EXEC sp_addrolemember N'db_owner', N'myuser'

I post it here hopefully it could help anyone.

Advertisement

Answer