1

We have a team of people, we all log in MSSQL Server from SSMS using our Active Directory accounts, by Windows Authentication.

Our AD accounts are added to an AD group, which is used to grant all permissions on MSSQL. For simplicity, we don't grant any permissions directly to our accounts, and for security we don't use local MSSQL accounts.

We're struggling to setup permission so that everybody (on the AD group) is able to list, view, edit, start, stop, enable, disable, etc MSSQL Agent jobs, and list and set proxies on CmdExec steps. It shouldn't matter who had created each job, we should all have full access to all jobs.

I had googled about it, read answers here, made a question myself. I read the SQL Server Agent Fixed Database Roles doc and noticed that SQLAgentOperatorRole is the most privileged role and still doesn't have full access to all jobs.

Is there any way to have this kind of permission set for an AD group?

If not, my next path will be to create a local MSSQL account, deny it permission to R/W on all databases, and then everybody will know its password and all jobs be created and managed from it. But I really wish to not do such thing.

Improve this question
1
  • Adding a relevant info I missed, all jobs are allowed to run under a proxy configured with an AD service account, which is also added to our group. This account isn't sysadmin and has the same permissions we have, so any job running on this proxy has the same permissions.
    – Hikari
    Jul 28, 2020 at 12:50

2 Answers 2

Reset to default
1

Only users in the sysadmin role can edit jobs they aren't the owner of via the SSMS object explorer so if you add all of the users to an AD group that is in the sysadmin server role, it will work, but you probably don't want to do that.

Improve this answer
1
  • Yeah, I don't want and can't do that :(
    – Hikari
    Jul 28, 2020 at 12:41
1

As @MSSQLServerDBA says only a sysadmin can alter another user's job or change the job's owner. But the reason for this is that any user who can do that is effectively a sysadmin.

SQL Agent TSQL Job Steps always connect as the Agent Service which is always a sysadmin, but before the job step Agent impersonates the job owner with

EXECUTE AS LOGIN = N'JobOwner' WITH NO REVERT

So if a user can alter a job owned by a sysadmin, or reassign a job to a sysadmin, that user can run arbitrary commands as a sysadmin.

Improve this answer
3
  • I understand that, but I think it's a worse security threat to put somebody as sysadmin to be used to manage jobs, than to allow him to manage them without being so.
    – Hikari
    Jul 28, 2020 at 12:43
  • The problem for us is that there may be a dozen ppl using MSSQL, and it's implausible to have only each person to be able to edit only jobs he created. He may not be available and somebody else need to do it. And have everybody as sysadmin also creates lots and lots of other issues.
    – Hikari
    Jul 28, 2020 at 12:45
  • If it's not possible to have multiple ppl manage all jobs, what's the 2nd best solution then, before putting everybody as sysadmin? Remove our group from SQLAgent* roles, create a MSSQL local account, deny R/W permissions on all DBs, and add it to SQLAgentOperatorRole? That's an ugly solution, but at least we avoid anybody creating jobs under their account, everybody has this MSSQL account's password and are unable to do anything with it other than manage jobs?
    – Hikari
    Jul 28, 2020 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.