We help IT Professionals succeed at work.

SQL Server 2005 Permisssions issue for Stored Procedure executing a Job

1,227 Views
Last Modified: 2008-04-24
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 ).  

ADDITIONAL INFO:  
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)

EXEC msdb.dbo.sp_update_jobschedule
 @job_name = 'enablePaymentsTriggers'
, @name = 'ScheduleEnablePaymentsTriggers'
, @enabled = 1
, @active_start_date = @rundate
, @active_start_time = @time

Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
The new user has identical access to the MSDB as the orginal user:
db_datareader
db_datawriter
SQLAgentUserRole

The stored procedure "sp_update_jobschedule" is not visible in the MSDB database via Management Studio and when I try to add permissions via the Query window (via SA login) I get the following error Message:

grant Execute on msdb.dbo.sp_update_jobschedule to krinehart

Cannot find the user 'krinehart', because it does not exist or you do not have permission.

Thanks,
   Kevin

Author

Commented:
Also:

To clarify, my statement above about the stored procedure is misleading.  

"The stored procedure "sp_update_jobschedule" is not visible in the MSDB database via Management Studio and when I try to add permissions via the Query window (via SA login) I get the following error Message"

The system stored procdure works fine and I do not need to grant any kind of permissions to it.  The problem is the Job is not visible to the user.  So I really need to enable the new user to see and execute the job listed above in the code.  

Author

Commented:
The correct error that I need to address is:
The specified @job_name ('enablePaymentsTriggers') does not exist.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.