Start Job with SP fails

[foxbox]

With MSSQL2K i started DTS jobs via a stored procedure. SP's i could start from ASP webpages, and all went very well.

The first clash with SQL 2005 was the move from DTS to SSIS. I have now DTSX structured files, and my current approach is to run these with a Job (Type=SSIS package, source file system)

I still need to start this from a webpage, so i created a SP with this line: exec msdb.dbo.sp_start_job '<jobname>'

With right-clik } Execute SP i tested this and everything looked oke.

But when i try to start the job from the webpage by executing the SP, this fails:

"The specified @job_name ('<jobname>') does not exist."

???!?!? Huh????? Manual this works A-oke!
So somehow the SP can not see the job.
I tried to fix that by changing the owner of the job and by adding a EXECUTE AS clause in the SP (both same user now), but now a ran into:

EXECUTE permission denied on object 'sp_start_job', database 'msdb', schema 'dbo'.

This looks like a classic: simply add my user to MSDB and GRANT execute rights for sp_start_job.

I did that, but the permission error message stays....


What additiinal setting(s) is/ae needed?

is there another/ better way to solve my problem (which is starting a SSIS package from a webpage)
????

 

[mrdenny]

Add the account that the web app uses to the SQLAgentUserRole in the msdb database. This will give them all the righst to start jobs. The job does need to be owned by the user however.

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