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).
Basit's SQL Server Tips 