I writing this blog post as a result of the following two questions, which I’ve been asked by one of my blog follower recently.

• Question#1: Does it matter who is the owner of the SQL Server Agent job?
• Question#2: Is there a way to quickly update the job owner for all SQL Server Agent jobs?

My replies to his questions are as follow:

Reply to question#1:

Yes, it does matter because when you run the SQL Server Agent job that is owned by a non-sysadmin user, SQL Server Agent logs into SQL Server using its own credentials, then switches the security context of that session to the login who owns the SQL Server Agent job. For example, if you have a SQL Server Agent job that is owned by user called “Joe Blob”.  Joe Blob is not a member of sysadmin fixed server role. When you run the SQL Server Agent job owned by him, the SQL Server Agent job will run under his security context. However, when the SQL Server Agent job is owned by the user who is the member of sysadmin fixed server role, the SQL Server Agent job runs under the security context of SQL Server Agent service account.

It is also a recommendation that individuals should not be set as the owner of the SQL Server Agent jobs, and dedicate account with least privileges assigned should own the SQL Server Agent Jobs. Because, setting individual’s as the job owner can lead to security issues when that individual’s account will be deleted from SQL Server or disabled in Active Directory. As a result, the SQL Server Agent job will stop working.

So be careful when creating or modifying the SQL Server Agent jobs because by default SQL Server Agent jobs are owned by the user who created or edited them. Running SQL Server Agent job with least privilege account is a best practice.

Reply to question#2:

You must be a system administrator to change the owner of a SQL Server Agent job. You can use SQL Server Management Studio to give others ownership of a SQL Server Agent job. For more information, see MSDN resource here.

Changing SQL Server Agent job ownership using SQL Server Management Studio is fairly simple, if you have few SQL Server Agent jobs. However, if you have hundreds of SQL Server Agent Jobs across numerous SQL Servers, then you must write Transact-SQL script using (msdb..sp_manage_jobs_by_login or msdb..sp_update_job), or write PowerShell script to quickly change the ownership of all SQL Server Agent jobs on SQL Server instances.

Change all SQL Server Agent Jobs Ownership (Transact-SQL version)

The following Transact-SQL version of the script, which I use to change the ownership of all SQL Server Agent job to specified user. In this script, I’m using msdb..sp_update_job system stored procedure to change the job ownership.

USE [msdb];

SET NOCOUNT ON;

DECLARE @NewOwnerLoginName [sysname] = N'New_Job_Owner_Login_Name' --// <-- Specify user to own all SQL Server Agent jobs

----------------------------------------------------------------------------------------------------
--// Internal script local variables											               //--
----------------------------------------------------------------------------------------------------
DECLARE @SQLStatementID01	[int] ,
		@CurrentCommand01	[nvarchar](MAX) ,
		@ErrorMessage		[varchar](MAX) 

IF OBJECT_ID(N'TempDb.dbo.#Work_To_Do') IS NOT NULL
    DROP TABLE #Work_To_Do 
CREATE TABLE #Work_To_Do
    (
      [SQLID] [int] IDENTITY(1, 1)
                    PRIMARY KEY ,
      [JobName] [sysname] ,
      [TSQL_Text] [varchar](1024) ,
      [Completed] [bit]
    )

INSERT  INTO #Work_To_Do
        ( [JobName] ,
          [TSQL_Text] ,
          [Completed]
        )
        SELECT  [name] ,
                'EXEC [msdb]..[sp_update_job] @job_name = N''' + [name]
                + N''', owner_login_name = N''' + @NewOwnerLoginName + N''';' ,
                0
        FROM    [msdb].[dbo].[sysjobs]

SELECT  @SQLStatementID01 = MIN([SQLID])
FROM    #Work_To_Do
WHERE   [Completed] = 0

WHILE @SQLStatementID01 IS NOT NULL
    BEGIN

        SELECT  @CurrentCommand01 = [TSQL_TEXT]
        FROM    #Work_To_Do
        WHERE   [SQLID] = @SQLStatementID01

        BEGIN TRY
            EXEC [sys].[sp_executesql] @CurrentCommand01
        END TRY
        BEGIN CATCH

            SET @ErrorMessage = N'"Oops, an error occurred that could not be resolved. For more information, see below:'
                + CHAR(13) + ERROR_MESSAGE() 

            RAISERROR(@ErrorMessage,16,1) WITH NOWAIT

            GOTO ChooseNextCommand
        END CATCH

        ChooseNextCommand:

        UPDATE  #Work_To_Do
        SET     [Completed] = 1
        WHERE   [SQLID] = @SQLStatementID01

        SELECT  @SQLStatementID01 = MIN([SQLID])
        FROM    #Work_To_Do
        WHERE   [Completed] = 0
    END

SET NOCOUNT OFF;

Change all SQL Server Agent Jobs Ownership (PowerShell version)

The following is the PowerShell version of the script, which I use to change the ownership of all SQL Server Agent job to specified user. In this script, I’m using Microsoft.SqlServer.Smo class and its methods to update the SQL Server Agent job ownership to specified user.

Function ChangeSQLAgentJobOwner
{
   # Declare powershell script parameters
   Param
   (
        [String] $SQLServer,
        [String] $NewOwnerLoginName
   )
   Try
   {
        [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Smo’) | Out-Null
        $SMOServer = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $SQLServer 
        $SQLAgent = $SMOServer.JobServer;

        $SQLAgent.Jobs | % { 
                                $_.set_OwnerLoginName($NewOwnerLoginName); 
                                $_.Alter(); 
                           }

   }
   	Catch [System.Exception]
	{
		Write-Host "Oops, an error occurred that could not be resolved. For more information, see below:"
		$Error[1]
	}

}

# Executing the powershell function
ChangeSQLAgentJobOwner "W7LAPT120" "New_Job_Owner_Login_Name" # Specify the new SQL Server Agent job owner login name

I hope you liked this post 🙂 . Please feel free to use comment section, If you have any question regarding this post. You can also drop me question via twitter (@BasitAali).