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 had read https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver15 and see 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.