0

NT group (created for developers) has login with the Sysadmin role on the Prod server to be able to execute, update, create, etc … SQL Server Agent jobs, run SSIS packages etc.

New requirements, that developers should not be sysadmin and not to be able to see some sensitive data.

What was done:

  • Login (for NT Group) – [dmn\group_name_dev_in_prod]

  • [dmn\group_name_dev_in_prod] was added to SSISDB as user and into ssis_admin role

  • [dmn\group_name_dev_in_prod] was added to msdb As user and into SQLAgentOperatorRole and also db_owner

  • [dmn\group_name_dev_in_prod] was added to every database used in their deployments with necessary rights

  • Proxy created and SQLAgentOperatorRole added to it

Now members of the group, can start any jobs, create any jobs, execute any jobs, but they cannot update/create existing steps.

The owner of all the jobs is sa. I cannot make [dmn\group_name_dev_in_prod] the jobs owner as its an NT group.

Is there way around it?

Ken White
  • 123,280
  • 14
  • 225
  • 444
yoelbenyossef
  • 393
  • 1
  • 7
  • 26
  • This cannot be done without sysadmin permissions, per the [docs](https://learn.microsoft.com/sql/relational-databases/system-stored-procedures/sp-update-jobstep-transact-sql#Permissions). That said, any permission restriction in SQL Server can always be worked around by dedicating a stored procedure to the job, and using `EXECUTE AS` (quick and dirty) or signing with a certificate (slow and clean), combined with granting users permission to execute the procedure. Of course this will mean users cannot use the SSMS interface to do it, but it's something. – Jeroen Mostert Oct 15 '21 at 20:16
  • Note that there is a really good reason for the sysadmin requirement that you should be aware of before making this possible: if a job is executed under different credentials, the ability to change a job step is a really simple way to mount a privilege escalation attack. All a user has to do is find a job executing with sysadmin permissions and update a step to do whatever they please as sysadmin (including reading sensitive data); this is why a custom stored procedure would still have to check that nothing of that sort is attempted. – Jeroen Mostert Oct 15 '21 at 20:18

0 Answers0