I was setting up some SQL Server agent jobs in a test environment. I needed one of our testers to be able to run the job on demand. I made the testers account the job owner and I created a proxy for the the job to run under. I granted the proxy appropriate rights for SSIS. The SSIS package needed permission on the share I added the testers account to the “SQLAgentUserRole” and asked the tester to try the jobs.
The job failed with the following error:
“Proxy (11) is not allowed for subsystem "SSIS" and user "xxx\xxxxxx". Grant permission by calling sp_grant_proxy_to_subsystem or sp_grant_login_to_proxy.
The 'sp_sqlagent_is_member' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. (Microsoft SQL Server, Error: 14516)”
This failed because the tester’s account did not have permission to use the proxy. To fix this I run the following script.
USE msdb ;
GO
EXEC dbo.sp_grant_login_to_proxy
@login_name = N'xxx\xxxxx',
@proxy_name = N'ProxyName' ;
GO
The tester was then able to run the job.