How to assign permission to run a job?

[katbear]

Hi,

My question is, how can you assign someone permission to run a single job, without giving them permission to run all jobs?

Does this person have to be a SQL Server login also?

Thanks

 

[mrdenny]

In SQL 2005 you make then a member of the SQLAgentUserRole and make them the job owner. There isn't a way to grant the user the right to start a job which they do not own.

In SQL 2000 the only way is to make them the job owner.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

So it seems that only ONE person can be the owner of a given job at a time? There can't be "multiple" job owners.

This goes against the advice I received from another person, who was suggesting that I could give several people permission to run a job... I wonder why they would say that?

 

[mrdenny]

Are you using SQL 2000 or SQL 2005?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

SQL 2005

 

[mrdenny]

You can add the user to the SQLAgentOperators role but this will give them rights to run all the jobs, not just a specific job.

There isn't a way to grant several users the right to run a single job.

The only possible way I can think to make this happen would be to create a proxy account, set the job to be owned by the proxy account. Have the users log in with that account and start the job.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[katbear]

Ok, thanks for the info

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com