Command failed: The EXECUTE permission was denied on the object X, database Y, schema 'dbo'

Question

I created new database-scoped credentials as well as a new target group to run my jobs on an Azure SQL Database and with the help of an Elastic Job Agent. The Elastic Job Agent is merely there and tied to the database on which I am running the job. Other than that, it does not play any part in running the following commands.

The job is only one step and in that step, I assign @credential_name to the credential that I created and @target_group_name to the newly created target group.

EXEC jobs.sp_add_job @job_name='UpdatePowerBIReport'
    , @description='Update the Power BI Report by calling the stored procedure';
 
-- And add the job step 
EXEC jobs.sp_add_jobstep 
      @job_name='UpdatePowerBIReport'
    , @command = N'EXEC dbo.UpdatePowerBI;'
    , @credential_name= 'ElasticJobUserCredential'
    , @target_group_name='MyTargetGroup'

As you see in the command, the job only runs a stored procedure called under the dbo schema called UpdatePowerBI.

When I run the job and monitor its execution it fails with the following message:

Command failed: The EXECUTE permission was denied on the object 'UpdatePowerBI', database 'MYDBNAME', schema 'dbo'. (Msg 229, Level 14, State 5, Line 1)

I am running this command in SQL Server Management Studio.

Martin Cairney Martin Cairney · Accepted Answer · 2020-12-17T23:47:03

On the target database, have you completed the following:

1 Created a USER with the same name and password as the Credential in the Job Database
2 GRANTED the EXECUTE permission to that USER on the Stored Procedure?

The error message is clear that the user context that is calling the procedure has not been granted the permissions to do so.

Command failed: The EXECUTE permission was denied on the object X, database Y, schema 'dbo'

1 Answers