Allow non-sysadmins to amend ''sa'' jobs

Question

Post reply

Allow non-sysadmins to amend ''sa'' jobs

osalnins

SSC Journeyman

Points: 91
More actions
May 2, 2005 at 4:33 am

#362519

Hi
Is there a known way to allow non-sysadmin users to amend jobs owned by 'sa'? I use MS SQL Server 2000 SP3.
--
Many thanks,
Osk

Viewing 8 posts - 1 through 7 (of 7 total)

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

Rafeeque M.K. Ten Centuries Points: 1205 More actions · Answer 1

Pls make it legible what u r searching for.That is what u mean by amend

osalnins SSC Journeyman Points: 91 More actions · Answer 2

Another way of asking this would be: Is there a known way to allow non-sysadmin users to modify jobs (i.e. change schedule, step details etc.) owned by 'sa'?

Hope this makes it clearer.

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 3

Well, no. Such SQL Server security hierarchy. There is one simple way to execute sa job, but then there must be a stored procedure, which runs a job, and you must be granted to execute this proc.

Cheers

Joshua Jones-214598 SSChasing Mays Points: 637 More actions · Answer 4

It is possible to grant advanced rights on jobs to non-'sa' accounts; but its not very pretty...

There's a role in msdb called TargetServersRole. Obviously, this is a role initially designed for use with the Master\Target server job admin capability; if you look at it, you'll see it has explicit permissions to a number of objects in msdb that control jobs. I've been able to copy this role, and modify the permissions on my new role to attain various levels of functionality in the Enterprise Manager GUI for non-'sa' logins.

Again, its not pretty, but it might work. The key is to keep tight control on that role, and make sure no one has access to it that shouldn't.

Good luck!

Cheers,

Joshua Jones

Linda Johanning SSCertifiable Points: 6727 More actions · Answer 5

I usually don't allow users in msdb at all, but when it's absolutely necessary I use the TargetServerRole and it works just fine. You can control the exact permissions that you want to give out.

Linda

DAVNovak Hall of Fame Points: 3120 More actions · Answer 6

One possible method is to use the DMO objects and create a custom front end for the SQL Server Scheduled jobs. This would take some work to develop, but once it is done, the users would be able to view the jobs and if you allow it, to edit the jobs and start/stop them.

Dave N

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 7

Well you learn learn and at last you see that there is no end... Thank you for suggestions. Earlier I didn't heard anything about that msdb role

Cheers