Hey Guys!
Bora para mais um artigo.
Introduction
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:
|
1 2 3 4 5 6 7 8 |
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
|
1 2 3 |
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
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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.
|
1 2 3 4 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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!
Portuguese 
English
O melhor site para se aprender recursos avançados de SQL Server!
Muito bom!
Show de bola