April 10, 2008 at 11:22 am
Trying to upgrade an application to SQL Server 2005 sp2, I have 4 or 5 department members who click a command button in Access which runs a stored procedure and in the stored procedure is EXEC MSDB..sp_update_schedule for an Agent job. They get "EXECUTE permission was denied on the object 'sp_update_schedule."
I cannot make one of them the owner of the job because all need to run it. I cannot make their Windows group owner of the job because evidently in SQL 2005 Windows groups can’t own jobs. I granted them ‘execute’ on sp_update_schedule, but that doesn’t solve it. They are not sysadmins and I don't want to make them sysadmin.
I have seen several online discussions of this issue that seem to basically say it just can’t be done. Does anyone know how I can give them the ability to update this schedule? Maybe I need to go some roundabout route like have the Access routine enter a record in a table and put a trigger on that table to run the job, but perhaps I would run into some permissions problem with that also. I would prefer to use the existing process if possible.
Thanks,
Holly
April 10, 2008 at 2:54 pm
Further on this problem, I have the Access routine inserting a record into a table, which has an insert trigger on it. The trigger runs a stored procedure which has sp_update_schedule, or I also tried sp_start_job. The account inserting the record in the table has exec permissions in msdb on those procedures.
Does anyone know how I can let a user who is not sysadmin run a job?????? We used to be able to do this in SQL 2000, and we have applications that depend on this function.
Any workarounds appreciated.
Holly
April 14, 2008 at 11:56 am
Just to close the loop, in case anyone has a similar challenge:
I tried Execute As, and also Certificates and Signed Procedures, and neither of those approaches allowed the user to update or start a job he did not own.
I could have had the user insert a record into a table, and set up a job to poll that table every three minutes, but I just hated to do that because it would be running all the time for just 10-15 instances per month.
In the end, the way I am running this is:
User in MS Access clicks button which runs a stored proc which only runs Raiserror(50101,1,1). This is an informational message I set up with sp_addmessage. Then I set up a SQL Agent Alert on message 50101, which has as Response to run the problem stored procedure which contains the sp_update_schedule statement. This is run by the SQL Agent service account, which is the job owner. So it allows me to escape the (lack of) ownership chaining.
Let's hope this doesn't get locked down in a future version!
Holly
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply