Sql-server – Unable to set user/group as principal on a proxy

jobspermissionsPROXYsql serversql-server-2012

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.

Best Answer

A post for handling job permissions can be found at:

Allow non-sysadmin, non-owner of a SQL Server Agent job to execute it

This post outlines:

  1. Using standard SQL Agent roles such as SQLAgentOperatorRole.
    Note: Active Directory Groups are not able to login to start a job.

  2. Erland Sommarskog's post http://www.sommarskog.se/grantperm.html#countersignatures outlines using countersignatures. This is a more complex setup, but it does work.

  3. Points to a custom StartAgentJob stored procedure to allow rights to be granted within the context of the procedure. This includes support of AD Groups as well. The custom StartAgentJob has code in it that explicitly allows members of an Active Directory Group (but not the Active Directory Group itself) to start a job that they do not own.

Related Question