November 22, 2001 at 6:09 am
I have a web content management system that pushes a transactional replication from a staging server to a live server every hour.
I want an "Accelerated Publication" process that triggers the replication process manually to force the publication early.
I have identified the job under management jobs and have got my web page to trigger it by saying:-
exec msdb.dbo.sp_Start_Job 'MyPushSubscription'
This works fine, but if I log in as another user then the stored procedure fails and says:-
The specified @job_name ('MyPushSubscription') does not exist.
1. Is this the correct way of doing this?
2. How do I grant access to the 'MyPushSubscription' job to other users?
Thanks
Dave
November 22, 2001 at 3:56 pm
Dave,
It fails because they are not the job owner. Quote from BOL on sp_start_job:
"A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns."
I had a very similar requirement early this year. Didnt have a lot of time to spend on it, so the solution I came up with was to keep the job owned by me, users send me a request via email to force the job to run. I have code in Outlook that looks for messages that meet the criteria, parses out what I need, then runs the job, updates a status page (static html), and lets the user know the job is in progress. The static page has mailto links that pre-set the subject line with the info I need.
Now that I think about it, what a hack!
Maybe a better solution would be to insert the same params in a table, then have a job running against it that would then run the required job. The downside to this is you've got to run the job very frequently to keep the response time reasonable, or put an insert trigger on the table to run the job that runs the job!
Other ideas anyone? I wouldnt mind a cleaner solution for my own setup.
Andy
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply