Changing the SQL Agent Job properties in one go

General 1 Minute

I was installing a new version of a vendor application and one of the requirements was to change the existing sql agent job owners to a different user account.

There were several jobs and I needed to find a way to script them out and do it all at once and all these jobs had the same naming convention, so,it was possible for me to do wild character search. Below is how I did,

Select
'EXEC msdb.dbo.sp_update_job @job_id=N'''+cast(Job_ID as varchar(100))+''',
@owner_login_name=N''NewJobOwner''' from msdb.dbo.sysjobs A
INNER JOIN master.dbo.syslogins B on A.owner_sid=B.sid
where A.enabled = 1 and A.name like '%--NAMEoftheJOBS--%' and
B.loginname= '--OldJobOwner--'

Copy the output of this into SSMS and Execute.

Alternatively, I could use loop or cursor and update one by one but I liked the above method.

Published by SQLWhisperer

Hello Everyone. My intention of having this site is to have one repository location for my SQL Server Learning's. Most of the information here is already available on Web but I want to have it in one global location, so I can reference whenever I need . I would be more than happy, if you find anything on this blog useful. Also, Please use your own caution when running any code on your systems.Thank you for visiting.

Published