Olá pessoal!
Bora para mais um artigo.

Introdução

Quem trabalha como DBA em ambientes ondes os desenvolvedores e analistas de BI do banco não são sysadmin (amém), mas criam jobs no banco utilizando seus próprios usuários (SQL Server – Entendendo as permissões e roles do SQL Agent (SQLAgentUserRole, SQLAgentReaderRole e SQLAgentOperatorRole)), sabe o quão comum são demandas de alteração de owner de jobs, especialmente quando um usuário sai da empresa e os seus jobs precisam ser transferidos para responsabilidade de outra pessoa.

Partindo desse cenário, gostaria de compartilhar com vocês como alterar o dono (owner) de um job do SQL Agent de forma prática, simples e rápida, tanto para um job específico quanto para todos os jobs de um usuário.

Como identificar o owner dos jobs do SQL Server Agent

Para iniciarmos esse artigo, vou demonstrar rapidamente como identificar o owner dos jobs do SQL Server Agent utilizando uma consulta simples T-SQL, onde será possível listar todos os jobs da instância e seus respectivos owners:

SELECT 
    A.job_id,
    A.[name] AS job_name,
    B.[name] AS [user_name],
    B.[sid]
FROM
    msdb.dbo.sysjobs A
    LEFT JOIN sys.server_principals B ON A.owner_sid = B.[sid]

Exemplo:

Como alterar o owner de um job com o SSMS ou Azure Data Studio

A forma mais simples e fácil de alterar o owner de um job é utilizando a interface do SQL Server Management Studio (SSMS).

Ou então, utilizar a interface do Azure Data Studio (aka SQL Operations Studio)

Como alterar o owner de um job com a sp_update_job

A forma mais simples de alterar o owner de um job utilizando scripts T-SQL é através da stored procedure de sistema, msdb.dbo.sp_update_job, que permite alterar várias configurações de um determinado job, entre eles, o owner do job.

Alterando o owner de um job a partir do nome do job

EXEC msdb.dbo.sp_update_job 
    @job_name = 'Teste de Job', -- sysname
    @owner_login_name = 'sa' -- sysname

Alterando o owner de um job a partir do ID do job

EXEC msdb.dbo.sp_update_job 
    @job_id = 'FEC4CB2F-A39D-4FCA-8594-9388348C5C65', -- sysname
    @owner_login_name = 'sa' -- sysname

Como transferir jobs entre logins com a sp_update_job

Quando você tem a necessidade de transferir a propriedade dos jobs de um usuário para outro, essa tarefa pode acabar sendo um pouco trabalhosa. Para auxiliar nessa tarefa, preparei um script para realizar essa operação para vocês.

DECLARE 
    @CmdUpdateJob VARCHAR(MAX) = '',
    @LoginOrigem VARCHAR(100) = 'dirceu.resende',
    @LoginDestino VARCHAR(100) = 'sa'


SELECT @CmdUpdateJob += '
EXEC msdb.dbo.sp_update_job @job_id = ''' + CAST(A.job_id AS VARCHAR(50)) + ''', @owner_login_name = ''' + @LoginDestino + ''';'
FROM
    msdb.dbo.sysjobs A
    JOIN sys.server_principals B ON A.owner_sid = B.[sid]
WHERE
    B.[name] = @LoginOrigem


EXEC(@CmdUpdateJob)

Exemplo da execução do script:

Como transferir jobs entre logins com a sp_manage_jobs_by_login

Como vimos acima, num cenário onde é necessário transferir os logins de um usuário para outro, podemos utilizar o script acima para fazer isso manualmente, utilizando a stored procedure sp_update_job. Entretanto, a partir do SQL Server 2008 podemos utilizar uma outra sp de sistema, a sp_manage_jobs_by_login, que permite transferir os jobs de um owner para o outro com apenas uma execução.

EXEC msdb.dbo.sp_manage_jobs_by_login
    @action = N'REASSIGN',  
    @current_owner_login_name = N'dirceu.resende',  
    @new_owner_login_name = N'sa';

Resultado da execução:

Como alterar o owner de todos os jobs

E por fim, caso você deseje alterar o owner de todos os jobs da sua instância, você pode utilizar manualmente a sp_update_job para aplicar essa mudança em seu ambiente com o script abaixo:

DECLARE 
    @CmdUpdateJob VARCHAR(MAX) = '',
    @LoginDestino VARCHAR(100) = 'sa'


SELECT @CmdUpdateJob += '
EXEC msdb.dbo.sp_update_job @job_id = ''' + CAST(A.job_id AS VARCHAR(50)) + ''', @owner_login_name = ''' + @LoginDestino + ''';'
FROM
    msdb.dbo.sysjobs A


EXEC(@CmdUpdateJob)

Bom pessoal, espero que vocês tenham gostado desse artigo e que ele lhes seja útil no dia a dia.
Um grande abraço e até a próxima!