Allowing Non-SA to change schedule

Question

Allowing Non-SA to change schedule

msi_99

SSC Veteran

Points: 250
More actions
April 22, 2009 at 12:18 pm

#208658

Does anyone have any recomendations on how to allow non-sa users to be able to change schedule on jobs that they do not own? No roles will work since the operator role will only give ability to run and not change. We do not want to give them SA rights.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

if you can make them use a stored procedure, you can use EXECUTE AS, so it runs under sa even though they invoke it;

is it more likely the users just want to run the job On Demand/Right Now, or do they REALLY need to change the scheduled time?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jerry Hung SSChampion Points: 12908 More actions · Answer 2

I believe this may also work, granting users to the roles in MSDB

http://technet.microsoft.com/en-us/library/ms188283.aspx

SQL Server 2005 introduced the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:
* SQLAgentUserRole
* SQLAgentReaderRole
* SQLAgentOperatorRole

I gave my developers a customer SQLAgentLimitedOperatorRole so they can execute SQL jobs, but cannot change it at all

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

msi_99 SSC Veteran Points: 250 More actions · Answer 3

SQL Agent Roles will not work. Only an owner or a sys-admin can change someone else's job.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

Actually, according to BOL, members of SQLAgentOperatorRole can enable and disable schedules they don't own.

4 SQLAgentOperatorRole members can enable or disable schedules they do not own by using the stored procedure sp_update_schedule and specifying values for the @enabled and the @schedule_id (or @name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail.

So, maybe you could create a selection of schedules that the users can choose from.

Greg