I'm organizing permissions on our SQL Server instance. I want to stop using a SQL Server account, and instead grant permissions to Active Directory groups, and then add user and service accounts to these groups. I'm also trying to remove sysadmin role from users.
I've followed instructions from Grant Admin to an Active Directory account in SQL Server and Deny users permission to create tables in master.
After doing those changes, everything seemed working... until I tried to manually start a job. I got the message
non-sysadmins have been denied permission to run dts execution job spets without a proxy account.
If I add the AD group to sysadmin it works. If I remove, it fails.
Following Running a SSIS Package from SQL Server Agent Using a Proxy Account I created a proxy. Then I started getting the message
Unable to start execution of step 1 reason jobowner doesn't have permission to use proxy 1 for subsystem.
How to give SQL Server Job Owner permission to Proxy a Credential explains how to set a user as principal to the proxy. It didn't work. I tried adding the group and a user. But when I click OK and reopen the dialog, the one I added isn't listed anymore.
So, basically, how to grant a non-sysadmin user permission to execute jobs? Preferably granting it to an AD group instead of specific users.