Proxy permissions in a Sql Server Agent job

I have a SSIS package which was failing to execute from a SQL Server Agent job with the following error:

Message
Unable to start execution of step 1 (reason: JobOwner EUR\s2sqlhotelldb01 doesn’t have permissions to use proxy 8 for subsystem SSIS). The step failed.

I solved the problem by changing the owner of the job to the account which is used for the proxy and then I granted the account the rights to use the proxy. Funny, but it works.

Here is the script:

USE msdb;

 GO

EXEC

dbo.sp_grant_login_to_proxy @login_name = N‘xxx\xxxxx’, @proxy_name = N‘ProxyName’;

 GO

 

 

1 comment to Proxy permissions in a sql server agent job

  • David Lathrop

    This part of the (weak) SQL Agent security. The basic logic behind the concept of SQL Agent proxies seems to be:

    If you want a non-DBA to be able to create/edit/run SQL Agent Jobs, you put them into the SQLAgentUserRole. They own the jobs they create, can only work with jobs that they own. You obviously don’t want someone like that being able to run jobs with the SQL Agent service account, so you can create proxy accounts and limit which ones they can use–but they have to use one of them. Now a different person can be in the SQLAgentUserRole, with a different set of proxies with permissions suitable for that person.

    That’s the basic logic behind the concept of SQL Agent proxies. Unfortunately, the owner of a job has to be a login (SQL or Windows user), and can’t be a role (e.g., for a development group).

    A similar logic exists for the SSIS packages, using the db_ssisltduser msdb role, except that now it is (1) an msdb role instead of a login, (2) it’s the package’s read-role/write-role instead of “owner,” and (c) there is no (documented) way to set up the access except the SSMS GUI.

    If you can just make all jobs owned by sa it’s really easy. I just use proxies when a step (a) runs code provided by a programmer, or (b) needs to access resources outside of the database server.

    Hope this helps.