1

I need to allow a user only to execute a specific Agent job, rather than be able to execute the other jobs that are there . Please advise how to accomplish this.

This user will only run a copy only backup before he updates the application and I don't want to give him access to the other jobs. Ola Hallengren scripts are used for that job.

I added the user to the DB_BackupOperator role. If I simply make him the owner of that job will he be able to run the other jobs that are owned by SA?

Paul White
  • 94,921
  • 30
  • 437
  • 687
SQL_NoExpert
  • 1,107
  • 1
  • 21
  • 37

1 Answers1

1

It'd be easier to just grant execute on Ola's proc to that user. Granted, here they'd be able to call the proc with whatever parameters they want. See Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it if you don't want to go that route. Depending on the route you take, you may duplicate the job and make them the owner of their own job.

While DB_BACKUPOPERATOR would be needed, it isn't enough here. If you read that answer it explains a way, but again it'd be easier to just let them execute the procedure or use a batch script and give them permission to run the batch script. Or use sqlcmd which Ola's scripts support - scsimon

If you make him the owner of that one job only and add him as a member of SQL Server Agent Fixed Database Roles SQLAgentUserRole should be able to execute the job. - sqlworldwide