Cool Tips‎ > ‎SQL Server Security‎ > ‎

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 INT CREATE TABLE #TempLog ( LogDate DATETIME, ProcessInfo NVARCHAR(50), [Text] NVARCHAR(MAX)) CREATE TABLE #logF ( ArchiveNumber INT, LogDate DATETIME, LogSize INT ) INSERT INTO #logF EXEC sp_enumerrorlogs SELECT @lC = MIN(ArchiveNumber) FROM #logF WHILE @lC IS NOT NULL BEGIN INSERT INTO #TempLog EXEC sp_readerrorlog @lC SELECT @lC = MIN(ArchiveNumber) FROM #logF WHERE ArchiveNumber > @lC END --Failed login counts. Useful for security audits. SELECT Text,COUNT(Text) Number_Of_Attempts FROM #TempLog where Text 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,Text FROM #TempLog where ProcessInfo = 'LOGON'and Text like '%SUCCEEDED%' and Text not like '%NT AUTHORITY%' Group by Text DROP TABLE #TempLog DROP TABLE #logF





ċ
Andy Hughes,
Jun 9, 2020, 5:41 PM
Comments