Hola, chicos,
¿Cómo estás?

Últimamente he visto un número considerable de DBA's con dudas sobre permisos y roles del Agente SQL Server y surgen muchas dudas sobre este tema en los grupos de Whatsapp, entre ellas:

  • ¿Cómo hago para que un usuario que no sea administrador de sistemas pueda ver los trabajos?
  • ¿Cómo hago para que un usuario que no sea administrador de sistemas pueda crear y ejecutar trabajos?
  • ¿Cómo hago para que un usuario que no sea administrador de sistemas pueda ejecutar cualquier trabajo?
  • ¿Cómo hago para que un usuario que no sea administrador de sistemas pueda cambiar la configuración del Agente SQL?
  • ¿Puede un usuario que no sea administrador de sistemas cambiar cualquier trabajo?

Mi intención en esta publicación es explicar las funciones del Agente SQL Server y responder estas preguntas.

Conocer los roles fijos del Agente SQL Server (MSDB)

El primer paso para responder las preguntas anteriores es explicar cuáles son y las diferencias entre las funciones fijas del Agente SQL Server.

Función de usuario del agente SQL

Esta es la función menos privilegiada del Agente SQL y permite a sus usuarios crear trabajos y desactivar/cambiar/ejecutar trabajos donde su usuario es el propietario del trabajo (es decir, que creó o que algún administrador de sistemas le asigna), pero no puede eliminar el historial de ejecución del trabajo, incluso si es el propietario de este trabajo.

Debido a esta restricción, los miembros de esta función solo pueden ver sus trabajos y solo la carpeta Trabajos y el Monitor de actividad laboral.

Incluso con este rol, puedo crear/eliminar/ejecutar trabajos:

Función de lector de agente SQL

Para ver los efectos del rol Función de lector de agente SQL, agregué el usuario “Usuario_Teste” a este rol. Los miembros de este rol tienen los mismos permisos que el rol. Función de usuario del agente SQL, pero pudiendo ver todos los trabajos de la instancia, incluso aquellos en los que el usuario que inició sesión no es el propietario.

Este permiso solo se aplica a la visualización/visualización de trabajos. Los miembros del rol Función de lector de agente SQL También solo pueden ejecutar/cambiar/eliminar trabajos que sean de su propiedad.

Si intenta ejecutar un trabajo donde su usuario no es el propietario, aunque pueda ver el trabajo, encontrará el siguiente mensaje de error:

Función de operador de agente SQL

Esta es la función más privilegiada del Agente SQL. Los miembros de esta función pueden ver toda la información del Agente SQL Server y ver todos los trabajos de instancia, como puede ver a continuación:

Además, los miembros de este rol pueden agregar Operadores, configurar alertas, administrar la configuración del Proxy, además de poder crear trabajos y ejecutar/activar/desactivar cualquier trabajo en la instancia, incluso aquellos de los cuales no son propietarios.

Sin embargo, ni siquiera los miembros de esta función pueden realizar cambios en trabajos de los que no son propietarios, como cambiar el cronograma del trabajo, modificar la configuración de la pestaña Pasos o cualquier otro cambio.

Incluso si intenta realizar cambios utilizando la línea de comando, no podrá realizar cambios en trabajos donde el usuario no sea el propietario. Solo miembros de rol administrador de sistemas Pueden realizar cambios en cualquier trabajo de la instancia, incluso aquellos de los que no son propietarios.

Respondiendo las preguntas

Ahora que he explicado las funciones del Agente SQL, respondamos las principales preguntas sobre este tema y eliminemos estas dudas de una vez por todas de la mente de los DBA.

¿Cómo hago para que un usuario que no sea administrador de sistemas pueda ver los trabajos?

Para que un usuario pueda ver los trabajos creados por él, simplemente agréguelo al rol Función de usuario del agente SQL. Para que pueda ver todos los trabajos en la instancia, será necesario agregarlo al rol. Función de lector de agente SQL. Con esto, también podrá crear trabajos y ejecutar/eliminar/cambiar los trabajos creados por él.

¿Cómo hago para que un usuario que no sea administrador de sistemas pueda crear y ejecutar trabajos?

Si la necesidad del usuario es solo crear y ejecutar los trabajos que crea, simplemente agréguelo al rol. Función de usuario del agente SQL, que es el rol con menos privilegios.

¿Cómo hago para que un usuario que no sea administrador de sistemas pueda ejecutar cualquier trabajo?

Para que el usuario pueda ejecutar cualquier trabajo en la instancia, incluidos los trabajos de los que no es el propietario, deberá tener el rol Función de operador de agente SQL, que es el rol con mayor nivel de privilegio. Tenga en cuenta que con esto también podrá eliminar el historial de trabajos, desactivar cualquier trabajo, agregar/cambiar/eliminar operadores y configuraciones de Proxy.

¿Cómo hago para que un usuario que no sea administrador de sistemas pueda cambiar la configuración del Agente SQL?

Incluso los miembros del rol Función de operador de agente SQL No se pueden realizar cambios en la configuración del Agente SQL. Si intenta realizar algún cambio en la configuración del Agente, verá este mensaje de error:

Sin embargo, esto se puede evitar si un miembro del rol de administrador del sistema otorga acceso EJECUTAR al procedimiento. sp_set_sqlagent_properties:

USE [msdb]
GO

GRANT EXECUTE ON dbo.sp_set_sqlagent_properties TO [Usuario_Teste2]
GO

Si se concede este permiso, el usuario podrá realizar cambios en la configuración del Agente SQL, incluso sin tener la función de administrador del sistema.

¿Cómo puedo hacer para que un usuario solo pueda ver los trabajos del Agente SQL y no pueda crearlos ni cambiarlos?

En muchas situaciones, es necesario conceder acceso para que otros usuarios puedan ver los trabajos, pero no desea que puedan crear o cambiar trabajos, incluso si utilizan al propio usuario como propietario de estos trabajos. En este caso, ¿qué rol le permite hacer esto? Desafortunadamente, ninguno. La función con menos privilegios en el Agente SQL, que es SQLAgentUserRole, ya permite al usuario crear y cambiar trabajos de los que es propietario.

Ante este escenario, el DBA Carolina Goltara encontró una manera creativa de lograr este objetivo. Ella me dio el consejo de agregar el usuario o grupo a la función SQLAgentReaderRole, para que pueda ver todos los trabajos y luego negar el permiso en los SP para manejar trabajos, usando los siguientes comandos:

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]

Como resultado, el usuario no podrá crear/cambiar/eliminar/ejecutar/detener ningún trabajo, pero podrá verlos todos.

¿Cómo hago posible que un usuario que no sea administrador de sistemas cambie cualquier trabajo?

No, no es posible. Por definición de seguridad, solo los miembros del rol de administrador de sistemas pueden realizar cambios en los trabajos creados por otros usuarios y cambiar el propietario de un trabajo.

Si fuera posible que otro usuario tuviera este poder, sería un fallo de seguridad importante en el Agente SQL, ya que los trabajos se ejecutan utilizando el usuario propietario del trabajo, con sus privilegios. Si este trabajo realiza alguna acción auditada, es el usuario del trabajo quien se registrará en esta rutina de auditoría.

Imagínese qué brecha de seguridad si un usuario que es miembro del rol Función de operador de agente SQL, pero que no es un administrador de sistemas podría cambiar el propietario de un trabajo y agregar cualquier otro usuario, incluso un administrador de sistemas, y este trabajo cambiaría datos incorrectos en Producción. El usuario que se registraría en los registros sería el usuario propietario del trabajo y no la persona que lo ejecutó.

Otra falla de seguridad grave podría ocurrir cuando hay un trabajo que se ejecuta en producción usando un inicio de sesión con privilegios db_owner en la base de datos. Si un usuario que no tiene el rol de administrador de sistemas pudiera editar libremente trabajos de los que no es propietario, muy bien podría cambiar el paso de uno de los trabajos que se ejecutan automáticamente en producción según un cronograma e insertar allí cualquier código, incluso concediéndose acceso a sí mismo a esta base de datos.

Por estas razones mencionadas anteriormente, Sólo los miembros del rol sysadmin pueden editar los trabajos de otros usuarios, así como cambiar el propietario del trabajo, ni siquiera con el privilegio CONTROL SERVER en la instancia un usuario que no pertenece al rol sysadmin puede hacerlo.

La única alternativa que veo para que un usuario que no sea administrador de sistemas pueda editar los trabajos de otros usuarios es crear una aplicación (Web o de escritorio) para crear esta interfaz de administración de trabajos, donde el usuario de la base de datos de la aplicación tiene privilegios de administrador de sistemas en la instancia. De esta forma, el control de acceso y permisos serían responsabilidad de la aplicación y los usuarios podrán gestionar trabajos, ya que el usuario de la aplicación será miembro del rol sysadmin.

Eso es todo, amigos.
Espero que hayas aclarado todas tus dudas sobre este tema.

Si tienes alguna duda déjala aquí en los comentarios.
¡Abrazo!