2

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

1
  • Can you tell us the exact procedure (and parameters) you use ? Can you also tell us your SQL Server version ? Is ola-hallengren procedures and tables created in the same database as your target database ?
    – Hybris95
    Oct 18, 2022 at 13:06

1 Answer 1

0

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

1
  • Thank you! I was looking for something like that on his page, but I was looking in all the wrong places I guess. I suppose I need to add my user to the db_owner role in the end then.
    – Widforss
    Oct 22, 2022 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.