TFS 2008: PCW fails with An internal error occurred on the report server. The EXECUTE permission was denied on the object 'xp_sqlagent_notify'

  • Article

Issue

When a new TFS project is created, you might see the following error

F30225: Error uploading report : Work Item with Tasks
Exception Details: The Project Creation Wizard encountered a problem while
creating reports on the SQL Server Reporting Services on <Server Name>.
The reason for the failure cannot be determined at this time.
Because the operation failed, the wizard was not able to finish.

 See the error log for more details. ---> The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.
SoapException

Cause

 xp_sqlagent_notify,xp_sqlagent_is_starting and xp_sqlagent_enum_jobs under Master Database did not have the execute permission for the RSEXECROLE.

Resolution

1. On the data-tier server, click Start, point to All Programs, point to Microsoft SQL Server, and then click SQL Server Management Studio.

2. In the Server type list, click Database Engine.

3. In the Server name list, click or type the appropriate server.

4. In the Authentication list, click the appropriate scheme.

5. In User name, type the user name of a valid account.

6. In Password, type the password of the account if SQL Server requires it, and then click Connect.

7. Expand Database

8. Expand System Databases

9. Expand Master Database

10. Expand Programmability

11. Expand Extended Stored Procedures

12. Under System Extended Stored Procedures

13. Right Click on sys.xp_sqlagent_notify, Seletect Properteis and click on Permissions Make sure that RSExecRole has Execute permssion

 Set same permissions for sys.xp_sqlagent_is_starting and sys.xp_sqlagent_enum_jobs

Content by: Romitg

Reviewed by: Lucky