Patrick Purviance – SQL Server DBA Blog
Blogging impactful solutions for SQL Server DBAs worldwide

SQL Agent Jobs owned by individual users – Quick Fix

Today I’m sharing a “quickly whipped script” to change the owner of SQL Agent Jobs to SA (or another of your choosing), so if the individual job owner were to leave the company and their AD account dropped, the job wouldn’t just begin failing. 

Feel free to use/modify/redistribute for any reason.

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) + ', @description = ' + char(39) + 
CASE WHEN j.[description] = 'No description available.'
	THEN 'Originally owned by ' + l.name + '.' + CHAR(39)
	ELSE j.[description] + CHAR(13) + '.  Originally owned by ' + l.name + '.' + CHAR(39)
END
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
WHERE l.[name] NOT IN ('sa', 'NT AUTHORITY\SYSTEM', 'NT SERVICE\MSSQLSERVER') 
ORDER BY j.[name]

Leave a Reply

Your email address will not be published. Required fields are marked *