We are running SQL Server 2005 which was upgraded from 2000 via a wizard. Our problem is that we had a user that was and is able to run a stored procedure that disables some file locking triggers and then sets a new start time for a particular job (which re-enables the triggers 60 seconds later) and then exits. This works fine for this user however when we add an additional user that executes this stored procedure the triggers are disabled but the reference to the job never executes. I have tried various levels of permissions and so far the only way I can get it to run for her is to add the sysadmin server role (which obviously I cannot do). The only other way that I was able to get it to work is to create a new job under her login (which will not work because we need this functionality in a stored procedure ).
1. The existing stored procedure was put in the original users schema by the SQL server 2005 installation wizard.
2. I have tried adding the new user to the original users schema with varying permissions which did not work.
3. The error message we receive is :
EXECUTE permission denied on object 'sp_update_jobschedule', database 'msdb', schema 'dbo'.
4. Here is the code from the stored procedure:
ALTER TABLE payments DISABLE TRIGGER mod_payment_closed
ALTER TABLE payments DISABLE TRIGGER delete_payment_closed
declare @rundate varchar(15)
declare @time varchar(15)
set @rundate = convert(varchar,getdate()+ '00:01:00' ,112)
set @time = convert(varchar,getdate() + '00:01:00',108)
set @time = left(@time,2) + substring(@time,4,2)+ substring(@time,7,2)
@job_name = 'enablePaymentsTriggers'
, @name = 'ScheduleEnablePaymentsTriggers'
, @enabled = 1
, @active_start_date = @rundate
, @active_start_time = @time