Update job owner for all SQL Server Agent jobs

I am helping a customer cleanup their SQL Server inventory, and remove a few risks that exist. In this case, they have more than 40 SQL Server Agent jobs that are owned by the domain login of their old DBA, who no longer works at the firm. They can't remove that login from the DC yet because the jobs that it owns will fail to run.

What login should be used as the Agent job owner?  There are many opinions here, but my rule is simple;  do not define SQL Server Agent job ownership with a domain login. What happens if when they remove the credentials, or even if DNS becomes inaccessible, and the login cannot be resolved? 

Use SA, or another SQL login that is created explicitly for Agent job ownership. In this tip I will show you how to identify all non-SA owned Agent jobs, and then update ownership to SA. 

FIRST, you run this SELECT to get a list of the jobs that you are going to effect. It's just a safety check, and will help you change back, if necessary. 

     /*  output a list of the jobs you'll update */ 

     SELECT a.name

     FROM msdb..sysjobs a JOIN sys.server_principals b

       ON a.owner_sid = b.sid

     WHERE a.enabled = 1  -- just looking for enabled jobs

     AND b.name <> 'sa'

Now we can alter the ownership for each to SA using a cursor, like this:

     /*  update job owner for all SQL Server Agent jobs */

     DECLARE @jobname VARCHAR(1000)

     DECLARE JobCursor CURSOR

     FOR

     SELECT a.name

     FROM msdb..sysjobs a JOIN sys.server_principals b

       ON a.owner_sid = b.sid

     WHERE a.enabled = 1  -- just looking for enabled jobs

     AND b.name <> 'sa'

     OPEN JobCursor

     FETCH NEXT FROM JobCursor INTO @jobname

     WHILE (@@FETCH_STATUS <> -1)

     BEGIN

          EXEC msdb..sp_update_job @job_name = @jobname, @owner_login_name = 'sa'

          FETCH NEXT FROM JobCursor INTO @jobname

     END

     CLOSE JobCursor

     DEALLOCATE JobCursor

Very simple, but we do have to ask another question. Should we use the SA account for job ownership?  I'll come back with another post tomorrow regarding SQL Server Agent's fixed database roles. Specifically, SQLAgentOperatorRole. We will use it to establish SQL Server Agent job ownership, using a non-SA account.

Back soon.