Olá pessoal,
Como vocês estão ?
Ultimamente, tenho visto uma quantidade considerável de DBA’s com dúvidas sobre as permissões as roles do SQL Server Agent e muitas dúvidas surgem sobre esse tema em grupos do Whatsapp, entre elas:
- Como fazer com que um usuário que não seja sysadmin possa visualizar jobs?
- Como fazer com que um usuário que não seja sysadmin possa criar e executar jobs?
- Como fazer com que um usuário que não seja sysadmin possa executar qualquer job?
- Como fazer com que um usuário que não seja sysadmin possa alterar as configurações do SQL Agent?
- Um usuário que não seja sysadmin pode alterar qualquer job?
Meu intuito neste post é explicar as roles do SQL Server Agent e responder essas perguntas.
Conhecendo as roles fixas do SQL Server Agent (MSDB)
O primeiro passo para responder as perguntas feitas acima, é explicar quais são e as diferenças entre as roles fixas do SQL Server Agent.
SQLAgentUserRole
Essa é a role menos privilegiada do SQL Agent e permite que seus usuários possam criar jobs e desativar/alterar/executar jobs onde o seu usuário seja o owner do job (ou seja, que ele criou ou que algum sysadmin atribui à ele), mas não pode apagar o histórico de execução do job, mesmo que ele seja o owner deste job.
Por conta dessa restrição, os membros dessa role podem visualizar apenas os seus jobs e visualizam apenas a pasta de Jobs e o Job Activity Monitor.
Mesmo com essa role, consigo criar/excluir/executar jobs:
SQLAgentReaderRole
Para visualizar os efeitos da role SQLAgentReaderRole, adicionei o usuário “Usuario_Teste” nesta role. Os membros dessa role possuem as mesmas permissões da role SQLAgentUserRole, mas podendo visualizar todos os jobs da instância, mesmo os que o usuário logado não seja o owner.
Essa permissão se aplica apenas a exibição/visualização dos jobs. Os membros da role SQLAgentReaderRole também só podem executar/alterar/excluir os jobs que eles são owners.
Se você tentar executar um job em que o seu usuário não seja o owner, mesmo que você esteja podendo visualizar esse job, você irá se deparar com a mensagem de erro abaixo:
SQLAgentOperatorRole
Essa é a role mais privilegiada do SQL Agent. Os membros dessa role podem visualizar todas as informações do SQL Server Agent e visualizar todos os jobs da instância, conforme você pode observar abaixo:
Além disso, os membros dessa role podem adicionar Operadores, configurar alertas, gerenciar as configurações de Proxy, além de poder criar jobs e executar/ativar/desativar qualquer job da instância, mesmo os que ele não é o owner.
Entretanto, mesmo os membros dessa role não podem realizar alterações em jobs em que ele não é owner, como alterar o schedule do job, modificar as configurações da aba Steps ou qualquer outra alteração.
Mesmo que você tente realizar as alterações utilizando a linha de comando, você não irá conseguir realizar alterações em jobs em que o usuário não é o owner. Apenas membros da role sysadmin podem realizar alterações em qualquer job da instância, mesmo os que ele não for o owner.
Respondendo às perguntas
Agora que já expliquei sobre as roles do SQL Agent, vamos responder às principais dúvidas sobre esse assunto e eliminar essas dúvidas de vez da mente dos DBA’s.
Como fazer com que um usuário que não seja sysadmin possa visualizar jobs?
Para que um usuário possa visualizar os jobs criados por ele, basta adicioná-lo na role SQLAgentUserRole. Para que ele possa visualizar todos os jobs da instância, ele precisará ser adicionado na role SQLAgentReaderRole. Com isso, ele também poderá criar jobs e executar/excluir/alterar os jobs criados por ele.
Como fazer com que um usuário que não seja sysadmin possa criar e executar jobs?
Se a necessidade do usuário é apenas criar e executar os jobs criados por ele, basta adicioná-lo na role SQLAgentUserRole, que é a role com menos privilégios.
Como fazer com que um usuário que não seja sysadmin possa executar qualquer job?
Para que o usuário possa executar qualquer job na instância, inclusive os jobs em que ele não é o owner, ele precisará estar na role SQLAgentOperatorRole, que é a role com o nível mais alto de privilégio. Tenha em mente que com isso, ele também poderá apagar o histórico dos jobs, desativar qualquer job, adicionar/alterar/excluir operadores e configurações de Proxy.
Como fazer com que um usuário que não seja sysadmin possa alterar as configurações do SQL Agent?
Mesmo os membros da role SQLAgentOperatorRole não podem realizar alterações nas configurações do SQL Agent. Caso você tente realizar qualquer alteração nas configurações do Agent, você verá essa mensagem de erro:
Entretanto, isso pode ser contornado caso algum membro da role sysadmin dê acesso de EXECUTE na procedure sp_set_sqlagent_properties:
|
1 2 3 4 5 |
USE [msdb] GO GRANT EXECUTE ON dbo.sp_set_sqlagent_properties TO [Usuario_Teste2] GO |
Caso essa permissão seja concedida, o usuário poderá realizar alterações nas configurações do SQL Agent, mesmo sem ser da role sysadmin.
Como fazer com que um usuário possa apenas visualizar os jobs do SQL Agent, não podendo criar ou alterar?
Em muitas situações, você precisa liberar acesso para que outros usuários possam visualizar os jobs mas não quer que eles possam criar ou alterar jobs, mesmo que sejam utilizando o próprio usuário como owner desses jobs. Neste caso, qual a role que permite fazer isso? Infelizmente, nenhuma. A role menos privilegiada no SQL Agent, que é a SQLAgentUserRole, já permite que o usuário possa criar e alterar jobs onde ele seja o owner.
Diante desse cenário, a DBA Caroline Goltara encontrou uma forma criativa de atingir esse objetivo. Ela me deu a dica de adicionar o usuário ou grupo na role SQLAgentReaderRole, para que ele possa visualizar todos os jobs, e depois negar a permissão nas SP’s de manipulação de jobs, utilizando os comandos abaixo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
USE [msdb] GO -- Bloquear criação DENY EXECUTE ON dbo.sp_add_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_add_jobschedule TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_add_jobserver TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_add_jobstep TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_agent_add_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_agent_add_jobstep TO [Usuario_Teste2] -- Bloquear alteração DENY EXECUTE ON dbo.sp_update_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_update_jobschedule TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_update_category TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_update_jobstep TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_update_schedule TO [Usuario_Teste2] -- Bloquear exclusão DENY EXECUTE ON dbo.sp_agent_delete_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_delete_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_delete_jobschedule TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_delete_jobserver TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_delete_jobstep TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_delete_jobsteplog TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_purge_jobhistory TO [Usuario_Teste2] -- Bloquear execução DENY EXECUTE ON dbo.sp_agent_start_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_stop_job TO [Usuario_Teste2] DENY EXECUTE ON dbo.sp_start_job TO [Usuario_Teste2] |
Com isso, o usuário não conseguirá criar/alterar/excluir/executar/parar nenhum job, mas ele conseguirá visualizar todos eles.
Como faço para um usuário que não seja sysadmin poder alterar qualquer job?
Não, não é possível. Por definição de segurança, apenas os membros da role sysadmin podem realizar alterações em jobs que foram criados por outros usuários e alterar o owner de um job.
Caso fosse possível que outro usuário tivesse esse poder, seria uma grande falha de segurança do SQL Agent, uma vez que os jobs são executados utilizando o usuário do owner do job, com seus privilégios. Se esse job executa alguma ação auditada, é o usuário do job que será gravado nessa rotina de auditoria.
Imagine que falha de segurança se um usuário membro da role SQLAgentOperatorRole, mas que não é sysadmin pudesse alterar o owner de um job e colocasse um outro usuário qualquer, até mesmo um sysadmin, e esse job alterasse dados incorretos na Produção. O usuário que ficaria gravado nos logs seria o usuário owner do job e não quem executou o job.
Uma outra falha grave de segurança poderia ocorrer onde exista um job que execute na produção utilizando um login com privilégios de db_owner no database. Se um usuário, que não seja da role sysadmin, pudesse editar livremente os Jobs em que ele não é o owner, ele poderia muito bem alterar o step de um dos jobs que são executados automaticamente na produção por um schedule e inserir qualquer código ali, inclusive, concedendo acessos para ele mesmo nesse database.
Por este motivos citados acima, que somente os membros da role sysadmin podem editar os jobs de outros usuários, bem como alterar o owner dos jobs, nem mesmo com o privilégio de CONTROL SERVER na instância um usuário que não pertence a role sysadmin consegue fazer isso.
A única alternativa que eu vejo para que um usuário que não seja sysadmin possa editar os jobs de outros usuários, é criando uma aplicação (Web ou Desktop) para criar essa interface de gerenciamento dos jobs, onde o usuário de banco da aplicação tenha privilégios de sysadmin na instância. Desta forma, o controle de acessos e permissões ficaria sob responsabilidade da aplicação e os usuários poderão gerenciar os jobs, uma vez que o usuário da aplicação será membro da role sysadmin.
É isso aí, pessoal.
Espero que tenham tirado todas as dúvidas sobre este assunto.
Qualquer dúvida, deixem aqui nos comentários.
Abraço!
Portuguese 
English
O artigo é ótimo, parabéns. Mas aqui ainda estou penando. Estou só estudando e tudo conecta normal com o meu usuário, exceto rodar o job. Configurei como vc falou no artigo, o msdb está com as 3 roles que vc menciona, além da db_owner. O erro insistente quando tento rodar é:
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user ‘BRQ\alexandrecortez’, error code 0x54b. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Mais um ótimo post e bem didático! Também me ajudou a entender melhor cada uma das permissões hehe.
Abraço.
Luiz Vitor
Luiz Vitor, espero ter ajudado. Também tinha dúvidas sobre esse tema, por isso fiz o post. Obrigado pela visita. Abraço!!
Fala grande Dirceu,
Seu post me salvou hoje! Rs Pra mim só do cara pertencer ao grupo SQLAgentOperatorRole ele conseguia editar jobs, steps e etc no qual ele não é o Owner.
Valeu pelo post 😉
Fábio, muito obrigado pelo feedback. É isso que nos motiva a sempre estar escrevendo e buscando assuntos legais para compartilhar. Espero que meu blog seja útil pra você em outras oportunidades.
Abraço.