September 6, 2011 at 7:26 am
hello guys, is there a way to give a user access to update and delete jobs that are not owned by the user without having to give them sysadmin rights? I know that by given them permissions to msdb, sqlagentoperatorrole, sqlagentreader and user role they can delete, update and create job by only owned by them.
September 6, 2011 at 8:19 am
AFAIK, there's no way.
If you take a look at msdb.dbo.sp_update_job, you will find this check hardcoded:
-- Check permissions beyond what's checked by the sysjobs_view
-- SQLAgentReader and SQLAgentOperator roles that can see all jobs
-- cannot modify jobs they do not own
IF ( (@x_owner_sid <> SUSER_SID()) -- does not own the job
AND (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1) -- is not sysadmin
AND (@enable_only_used <> 1 OR ISNULL(IS_MEMBER(N'SQLAgentOperatorRole'), 0) <> 1))
BEGIN
RAISERROR(14525, -1, -1);
RETURN(1) -- Failure
END
I'm sorry, I don't think it's possible.
-- Gianluca Sartori
September 6, 2011 at 8:54 am
Gianluca is correct, there is no way, there should be IMHO but there isn't.
Best you can do short of sysadmin is have all jobs owned by the same SQL authenticated account and let everyone log on with that account.
---------------------------------------------------------------------
September 6, 2011 at 1:27 pm
thank you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply