Change SQL Agent Job Owner

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: ChillyDBA

Date: 9 Jun 2020

Description

One of the most common annoyances when DBA team memberships changes, is the fact that any SQL Agent jobs owned by them will now fail to run. Not fail, but fail to run - so there won't even be a fail code reported.

This is firmly supported in it's annoyance level by the fact that there is no GUI or system proc method for changing job ownership. It must be done pro grammatically.

Of course, if only the default job owner was sa and not the logged in user, then this problem would likely not be as prevalent.

Anyway, the code below does the trick.

Test it first though.... :-)

Code

DECLARE @TSQL NVARCHAR(2000)DECLARE @lC INTCREATE TABLE #TempLog (LogDate DATETIME,ProcessInfo NVARCHAR(50),[Text] NVARCHAR(MAX))CREATE TABLE #logF (ArchiveNumber INT,LogDate DATETIME,LogSize INT)INSERT INTO #logFEXEC sp_enumerrorlogsSELECT @lC = MIN(ArchiveNumber) FROM #logFWHILE @lC IS NOT NULLBEGININSERT INTO #TempLogEXEC sp_readerrorlog @lCSELECT @lC = MIN(ArchiveNumber) FROM #logFWHERE ArchiveNumber > @lCEND--Failed login counts. Useful for security audits. SELECT Text,COUNT(Text) Number_Of_AttemptsFROM #TempLog whereText like '%failed%' and ProcessInfo = 'LOGON'Group by Text--Find Last Successful login. Useful to know before deleting "obsolete" accounts. SELECT Distinct MAX(logdate) last_login,TextFROM #TempLogwhere ProcessInfo = 'LOGON'and Text like '%SUCCEEDED%'and Text not like '%NT AUTHORITY%'Group by TextDROP TABLE #TempLogDROP TABLE #logF