Wednesday, November 18, 2015

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 */ 
     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 <> '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)
     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 <> 'sa'

     OPEN JobCursor
     FETCH NEXT FROM JobCursor INTO @jobname
     WHILE (@@FETCH_STATUS <> -1)
          EXEC msdb..sp_update_job @job_name = @jobname, @owner_login_name = 'sa'
          FETCH NEXT FROM JobCursor INTO @jobname
     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.  

No comments:

Post a Comment