Changing SQL Server Job Owners against the SQL Server Enterprise

Last night on twitter an interesting question was asked by @just_samson using the #SQLHelp hash tag. He asked, “can you change the owner for jobs &dbs across instances using Policy Based Management and Central Management Server?”  The answer to this question is no because an agent facet doesn’t exist. Users cannot create facets.  Does this mean he is dead out of the water? No, because good old T-SQL and CMS can be leveraged to get the job done.

Today we are going to focus on changing SQL Agent Job owners.

How do we find owners?

SELECT
    sv.name AS [Name],
    sv.job_id AS [JobID],
    l.name AS UserName
    FROM
    msdb.dbo.sysjobs_view AS sv
    INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
    ORDER BY
    sv.[Name] ASC

Here is a screen shot from my demo so you can follow along.

How do we change the job owner for all jobs?

You can see that the first job “Device by Zero” has the owner name set to “PBMDEMO\Administrator.” In this example we will want to change the owner to be “sa”. The following script below will do this. The only problem is you would have to run this script against every instance. That’s where the Central Management Server comes into play.

** This script is used as demo. It will only work against SQL 2005\2008\2008R2. Run it on development before you consider using it in production. If you decide to run it in production you are on your own. Run it at your own risk. **

DECLARE @JobID uniqueidentifier
DECLARE @NewOwner varchar(200)
DECLARE @OldName varchar(200)

SET @NewOwner = 'sa'
SET @OldName = 'PBMDEMO\Administrator'

SELECT
sv.name AS [Name],
sv.job_id AS [JobID],
l.name AS [OwnerName]
INTO #SQLJobs
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
WHERE l.name like @OldName
ORDER BY
sv.[Name] ASC

SELECT * FROM #SQLJobs
WHILE (SELECT COUNT(*) FROM #SQLJobs ) > 0 BEGIN
    SELECT TOP 1 @JobID = JobID FROM #SQLJobs
    EXEC msdb.dbo.sp_update_job @job_id= @JobID,
        @owner_login_name=@NewOwner
        DELETE FROM #SQLJobs WHERE JobID = @JobID

END

DROP TABLE #SQLJobs

Now assuming you have Central Management Server configured just right click on the group of servers you want the change to be applied to and select “New Query.” Copy and paste the code and you should be able to execute the query.  Below are some screenshots from my demo incase you are new to Central Management Server. We will apply the script against the Production group.

Finally if we execute the initial query we will see that all jobs have “sa” as the owner.

Other Related Articles:

• Changing SQL Server Job Owners with Powershell via Sean McCown (b | t)
• Being Proactive with Central Management Server
• Policy-Based Management Myths – Three Part Series (#1, #2, #3)