Can't start a job that was created by user A from user B

Hello all,
I have a job I created with user A, but I need user B to be able to execute
this job programatically. I've given user B permission to EXECUTE the
"msdb.dbo.sp_start_job" sproc and the user is able to execute it but with an
error thrown. The following is the error I get when I run it with user B.
This error does not appear when I run it with user A.
The specified @.job_name ('MyJobName') does not exist.
After doing some research by looking at the system sprocs, I found that the
error is thrown from a call made to 'msdb.dbo.sp_verify_job_identifiers'. I
researched that sproc and then found the system code that is throwing the
error, listed below.
-- Check if the job name is ambiguous
IF ((SELECT COUNT(*)
FROM msdb.dbo.sysjobs_view
WHERE (name = @.job_name)) > 1)
BEGIN
RAISERROR(14293, -1, -1, @.job_name, @.name_of_id_parameter,
@.name_of_name_parameter)
RETURN(1) -- Failure
END
I performed a SELECT * query to the sysjobs_view view with user B and 0 rows
were returned. So it appears that the view is filtering the results by user
and the job is not found which is causing the error.
Do you guys know of a way I can make this work? I would appreciate any help.
Thanks!
JohnnyHi Johnny,
Only members of the sysadmin fixed role or the jobowner can start a job.
HTH
Adam
--
Adam J Warne, MCDBA
"Johnny" wrote:
> Hello all,
> I have a job I created with user A, but I need user B to be able to execute
> this job programatically. I've given user B permission to EXECUTE the
> "msdb.dbo.sp_start_job" sproc and the user is able to execute it but with an
> error thrown. The following is the error I get when I run it with user B.
> This error does not appear when I run it with user A.
> The specified @.job_name ('MyJobName') does not exist.
> After doing some research by looking at the system sprocs, I found that the
> error is thrown from a call made to 'msdb.dbo.sp_verify_job_identifiers'. I
> researched that sproc and then found the system code that is throwing the
> error, listed below.
> -- Check if the job name is ambiguous
> IF ((SELECT COUNT(*)
> FROM msdb.dbo.sysjobs_view
> WHERE (name = @.job_name)) > 1)
> BEGIN
> RAISERROR(14293, -1, -1, @.job_name, @.name_of_id_parameter,
> @.name_of_name_parameter)
> RETURN(1) -- Failure
> END
>
> I performed a SELECT * query to the sysjobs_view view with user B and 0 rows
> were returned. So it appears that the view is filtering the results by user
> and the job is not found which is causing the error.
> Do you guys know of a way I can make this work? I would appreciate any help.
> Thanks!
> Johnny
>|||Thanks. I didnt want to do that but I guess I have no choice.
Johnny
"Adam Warne" wrote:
> Hi Johnny,
> Only members of the sysadmin fixed role or the jobowner can start a job.
> HTH
> Adam
> --
> Adam J Warne, MCDBA
>
> "Johnny" wrote:
> > Hello all,
> >
> > I have a job I created with user A, but I need user B to be able to execute
> > this job programatically. I've given user B permission to EXECUTE the
> > "msdb.dbo.sp_start_job" sproc and the user is able to execute it but with an
> > error thrown. The following is the error I get when I run it with user B.
> > This error does not appear when I run it with user A.
> >
> > The specified @.job_name ('MyJobName') does not exist.
> >
> > After doing some research by looking at the system sprocs, I found that the
> > error is thrown from a call made to 'msdb.dbo.sp_verify_job_identifiers'. I
> > researched that sproc and then found the system code that is throwing the
> > error, listed below.
> >
> > -- Check if the job name is ambiguous
> > IF ((SELECT COUNT(*)
> > FROM msdb.dbo.sysjobs_view
> > WHERE (name = @.job_name)) > 1)
> > BEGIN
> > RAISERROR(14293, -1, -1, @.job_name, @.name_of_id_parameter,
> > @.name_of_name_parameter)
> > RETURN(1) -- Failure
> > END
> >
> >
> > I performed a SELECT * query to the sysjobs_view view with user B and 0 rows
> > were returned. So it appears that the view is filtering the results by user
> > and the job is not found which is causing the error.
> >
> > Do you guys know of a way I can make this work? I would appreciate any help.
> > Thanks!
> >
> > Johnny
> >sql