January 9, 2013 at 2:14 pm
I have an OLTP system which has a monitor job which needs to be run during the day. WHen the closing is run by 2 different sites the job needs to be disabled. The time varies between 7 and 9 during the night. Any idea of a way to grant this to a user to only be able to disable this specific job, is there a way to create a custom admin role for the agent?
January 9, 2013 at 8:55 pm
timscronin (1/9/2013)
I have an OLTP system which has a monitor job which needs to be run during the day. WHen the closing is run by 2 different sites the job needs to be disabled. The time varies between 7 and 9 during the night. Any idea of a way to grant this to a user to only be able to disable this specific job, is there a way to create a custom admin role for the agent?
The user would need to be part of the SQLAgentReaderRole Role in msdb and own the job but that scenario is probably undesirable.
Another option is to create a procedure that calls msdb.dbo.sp_update_job to disable only the job in question. The proc would need to be signed with a certificate in the msdb database which would elevate the users permissions, but only for purposes of executing the code in the proc. You would add the certificate and proc to the msdb database and grant exec on the proc to the low-priv user. Then the user can exec the proc to disable the job in question, but not do anything else with SQL Agent including viewing the jobs via SSMS.
The "Complete Example" in this article will get you started down that path if needed. The sample can be modified to put the certificate in msdb and the proc definition could call sp_update_job. Tutorial: Signing Stored Procedures with a Certificate
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 11, 2013 at 6:52 am
My workplace does this frequently. We have a job that runs every day to check the date. On month end, or other relevant dates, this job disables the other job. If the other job is disabled and it's after the relevant date, this job reanbles the other job.
January 11, 2013 at 10:44 am
Perhaps you can give the users access to a table where they can set a flag to disable the job. Then add a step to the job that will check this flag and if it is set cause the job to terminate. I use this method when I want to give users the ability to kick something off server side without having to delve into security configurations.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply