Printed 2017/08/23 09:33PM

SQL Server Agent – Proxy error

By Gethyn Ellis, 2010/07/09

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 ;

EXEC dbo.sp_grant_login_to_proxy
@login_name = N'xxx\xxxxx',
@proxy_name = N'ProxyName' ;

The tester was then able to run the job.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.