http://www.sqlservercentral.com/blogs/sqldbauk/2010/07/09/sql-server-agent-_1320_-proxy-error/

Printed 2014/07/26 05:28AM

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 ;
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.



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.