Grant an AD Group permission to edit all MSSQL Agent jobs 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.
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.