I'm planning to orchestrate my index maintenance with other jobs in my Azure SQL Database (serverless) using Azure Data Factory. The job will be run by the Managed Identity of my ADF service, and the MI has been added to the "db_datareader", "db_datawriter" and "db_ddladmin" roles, as well as been granted EXECUTE rights.
The statistics update worked, but after that I got this error:
Sql error number: 50000. Error Message: Msg 297, The user does not have permission to perform this action.
What permission(s) is my MI missing? I would like to avoid making it "db_owner" if possible
Best Answer
Considering Ola Hallengren recommendations :
So short answer :
sysadmin user for a SQL Server Agent service Account
If you have to use a specific user :
EXECUTE on dbo.IndexOptimize
VIEW DEFINITION on dbo.CommandExecute
VIEW DEFINITION on dbo.CommandLog
VIEW SERVER STATE
db_owner on all target databases
References :
https://ola.hallengren.com/frequently-asked-questions.html