July 22, 2009 at 8:46 pm
i have a simple question...i am looking if there is anyway the user can ONLY change the owner of the job without giving him sysadmin access..please suggest....i do not want to deny permissions on any of the existing securables...thanks..
July 23, 2009 at 2:31 am
I don't think that it is possible
July 23, 2009 at 4:48 am
please try the below things to change ownership to sa
============================================
how to change ownership of job to sa
========================================
fire the below sql stmt; it will generate exec sp cmds, select the specific one according to the job and execute; it will change the owner to sa
================================================================================================================================================
SET NOCOUNT ON
SELECT 'EXEC MSDB.dbo.sp_update_job ' + char(13) +
'@job_name = ' + char(39) + j.[Name] + char(39) + ',' + char(13) +
'@owner_login_name = ' + char(39) + 'sa' + char(39) + char(13) + char(13)
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
WHERE l.[name] 'sa'
ORDER BY j.[name]
you can confirm for change by firing the below sql cmd;
==========================================================
SELECT j.[name] AS 'JobName',
Enabled = CASE WHEN j.Enabled = 0 THEN 'No'
ELSE 'Yes'
END,
l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]
GO
July 23, 2009 at 8:14 am
thank you for responding..i do not want the user to change to sa because he will not be able to run as only sa can run the job if owner is sa..for example if "john" is the owner of the job..and if "smith" wants to change the owner as "smith"..he should be able to do it..john and smith have same permissions...so without gving them sysadmin..can this be done?..thanks..again
July 23, 2009 at 5:30 pm
It can't be done. Even the SQL Agent database roles in msdb can't change job ownership.
Greg
July 24, 2009 at 8:44 am
hmm ok..thanks Greg....i was just wondering isnt there a procedure like "sp_changeonwner" something which i can assign...i will also do some research and see..thanks,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply