Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Entendendo as permissões e roles do SQL Agent (SQLAgentUserRole, SQLAgentReaderRole e SQLAgentOperatorRole)

Visualizações: 14.228 views
Tempo de Leitura: 7 minutos

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:

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:

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!