What permissions are needed for a user running Ola Hallengren's IndexOptimize stored procedure?

Question

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

Answer 1

Considering Ola Hallengren recommendations :

Which permissions are needed for the SQL Server Maintenance Solution to work? If you are using SQL Server Agent, the jobs run under the SQL Server Agent service account that is a member of the sysadmin server role. If you are using a proxy account, I recommend that the account be a member of the sysadmin server role.

If you are using another scheduler, I recommend that the scheduler run under an account that is a member of the sysadmin server role.

If you need to have users execute the stored procedures ad hoc against specific databases, these permissions are needed:

IndexOptimize: EXECUTE on dbo.IndexOptimize, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

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