Grant access to user to run a SQL agent job

Question

Grant access to user to run a SQL agent job

Robin35

SSCertifiable

Points: 6471
More actions
November 14, 2016 at 9:13 am

#401045

Hello,
I wanted to set up a backup agent job in SQL 2008 instances and instead of scheduling i would like to give the user to kick off the backup job. i do not want to schedule because we dont know when the application process completes and we would like to kick off once their process completes.
Can anyone please provide how to give access to user ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

There are three roles in msdb, called something like SQLAgentUser, SQLAgentOperator and SQLAgentSomethingElse. Each gives a different level of permissions to a user to do things with jobs. One of them will provide the access you're looking for - you just need to read about them to decide which.

John

ScottPletcher SSC Guru Points: 101105 More actions · Answer 2

You have a couple of choices.

If you are willing to allow them to run any (local) job, you can add them to the SQLAgentOperatorRole in msdb.

If you want them to be able to run just that one job, then you need to create a separate proc to run the job, and create it with "EXECUTE AS [other_user_name]". Be sure to give user "other_user_name" all backup permissions needed, etc., possibly even sysadmin-level permissions. Finally, give them permission to run that proc, which will in turn run the job.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Robin35 SSCertifiable Points: 6471 More actions · Answer 3

ScottPletcher (11/14/2016)
You have a couple of choices.
If you are willing to allow them to run any (local) job, you can add them to the SQLAgentOperatorRole in msdb.
If you want them to be able to run just that one job, then you need to create a separate proc to run the job, and create it with "EXECUTE AS [other_user_name]". Be sure to give user "other_user_name" all backup permissions needed, etc., possibly even sysadmin-level permissions. Finally, give them permission to run that proc, which will in turn run the job.

Thanks Scott. It worked using the proc, i just gave him execute permission on that proc.