Friday, 9 July 2010

SQL Server Agent – Proxy error

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.

No comments:

Post a Comment

Featured post

OUT OF SUPPORT: SQL Server 2008 and SQL Server 2008 R2

Welcome to this post, on SQL Server 2008 and 2008 R2, both of these versions of SQL server will go out of  extended support with Microsoft t...