2

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.

3

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.

  • Thanks. I have verified and the AD group is already member of SQLAgentOperatorRole on msdb. And still I'm unable to start the job when that group isn't sysadmin. Any idea to discover what's blocking/denying it? – Hikari Apr 6 '16 at 16:17
  • Is msdb owned by the sa login (it normally is) or another login that is itself defined as a sysadmin? – RLF Apr 6 '16 at 17:49
  • Yes it's owned by sa. – Hikari Apr 7 '16 at 12:42
  • @Hikari - Sorry, I misunderstood your response and missed the point. I have clarified that the StartAgentJob code takes explicit action to determine if the Login account will be accepted to indicate the job should be started. – RLF Apr 7 '16 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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