How to allow non-sa to modify a job? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to allow non-sa to modify a job?

Author

Topic

XonicEQ
Starting Member

5 Posts

Posted - 2008-01-15 : 17:12:57

Is there a way to allow non-sysadmin to modify jobs they don't own from the management studio interface?

Even the most privileged SQLAgentOperatorRole does not allow this...

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-15 : 23:11:48

Try grant exec permission on proper sps to them, you should get sp name from error message.

sqlsquirrel
Starting Member

21 Posts

Posted - 2008-01-16 : 17:08:49

Try adding the non-sysadmin to the TargetServersRole in msdb. In SQL Server 2000... this allowed non-sysadmins to VIEW the jobs... not sure if they changed that ability in 2005. Give it a try.

Good Luck!

Brett Davis
Senior SQL Server DBA
For more helpful tips checkout my blog at: http://www.lockergnome.com/sqlsquirrel/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-16 : 23:25:55

Member of that role can see jobs owned by other user but can't modify.

XonicEQ
Starting Member

5 Posts

Posted - 2008-01-17 : 11:16:04

Yeah, they can only disable/enable jobs and schedules.

I guess there is no built-in way to accomplish this. I'll have to look at modifying the system procedures to allow this.

If anyone have tips, please share.

Thanks everyone.

XonicEQ
Starting Member

5 Posts

Posted - 2008-01-17 : 20:36:55

I find out we can modify the system store procs, like sp_update_schedule etc to allow non-sa, non-owner to execute sp_update_job to modify jobs. However, from the Management Studio interface, those options are still greyed out for the those users.

Any idea?

montu
Yak Posting Veteran

60 Posts

Posted - 2008-01-17 : 22:14:05

add that user to dbcreator

XonicEQ
Starting Member

5 Posts

Posted - 2008-01-18 : 11:14:50

Thanks but dbcreator role did not help.

TargetServersRole didn't allow them to make changes via Management Studio interface neither, options are still all grey'ed out

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-01-19 : 21:09:30

Tried grant exec permission on sp_update_schedule to user?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-19 : 21:21:31

i don't think the answer is going to get any better than the ones u got here http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=83&threadid=88859&enterthread=y

as one has to wonder why u would expose so much security for this. why not just make developer or dev group owner?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-23 : 12:07:05

add users to SQLAgentOperatorRole

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources