locked
Cannot create project (TF30225: xp_sqlagent_notify)

Answers

  • I called MS support and got it resolved.

    The nt authority\network service account was missing a permission. We added RSExecRole role to the master database.


    Thursday, August 24, 2006 6:07 PM

All replies

  • I called MS support and got it resolved.

    The nt authority\network service account was missing a permission. We added RSExecRole role to the master database.


    Thursday, August 24, 2006 6:07 PM
  • Can you walk me through that procedure. I am having the same problem.
    Thursday, August 31, 2006 7:42 PM
  • Hey AbsoluteIT,

    I had the same problem when I tried to update the reports after upgrading from RC to RTM (the reports had been updated between versions). I got around the problem at the time by manually importing the reports one by one from the template. Then I had the exact same problem when I tried to create a new team project. Just when I thought I had removed all problems....

    To fix, create a new database role in master and msdb: RSExecRole. In "Securables" add the following:

    master:
      xp_sqlagent_notify (execute)
      xp_sqlagent_enum_jobs  (execute)
    msdb:
      sp_add_category  (execute)
      sp_verify_job_identifiers  (execute)
      sysjobs (select)

    Assign the role from each db to Network Service.

    This fixed it for me. Not sure if will work for everyone as each case might be different, but again, it worked for me, I can now create team projects without any errors.

    As to why I had this problem - a couple of factors. I had no problems at all with installation. But I deleted the encryption key from Reporting Services since I couldn't remember the passphrase for it, and then created a new encryption key and recreated the datasources etc. Maybe this process removed these permissions on master and msdb???

    Enjoy!

    Thursday, October 05, 2006 4:23 AM
  • Thanks Scotty,

    this really fix the error.

    I try and works.

    Regards,

    Abu

    Monday, April 27, 2009 7:26 PM
  • Hi. I've got a similar problem with reporting service subscriptions.

    I've setup the roles you've specified, unfortuanately I've still come across the same error.

    The only step I'm not too certain about is the "Assign the role from the each db to Network Service".

    Perhaps rephrase that. Are you suggesting I go to my db, go to security->roles->database roles-> and then....? I'm not too sure what you mean by this step.

    Thanks


    Tuesday, September 15, 2009 10:20 AM
  • After reading some more posts, it seems like I need to:

    Go to System Databases->master->Security->Users->  And assign the role to the Network Service User.

    I don't have a network service user setup.

    Is my assumption correct and do I need to setup that user?

    Lastly, my datasource in the RS uses a specific user.
    Tuesday, September 15, 2009 10:50 AM
  • I'VE GOT IT. Okay, sometimes its a bit confusing understanding what other mean, so I'll try to be clear and specific towards describing my solution.

    1) You need to setup the roles Scotty suggested above.

    To fix, create a new database role in master and msdb: RSExecRole. (Databases -> System Databases -> master -> Security -> Roles -> Database Roles) and (Databases -> System Databases -> msdb-> Security -> Roles -> Database Roles) In "Securables" add the following:

    master: 
      xp_sqlagent_notify (execute) 
      xp_sqlagent_enum_jobs  (execute) 
    msdb: 
      sp_add_category  (execute) 
      sp_verify_job_identifiers  (execute) 
      sysjobs (select)

    2) Because you already have reporting services setup. There should be two standard reporting service tables setup. One I think by default is named ReportServer$SQL2005  and the other ReportServer$SQL2005TempDB. The RSExecRole needs to be setup there aswell. Now becuase mine was already configured, I'm not too sure what the permissions of this role should be.

    3) Now this is the part that may be configured differently depending on your Environment. 

    3.1)Now Scotty suggested we give the NTUser, which can be found in  (Databases -> System Databases -> master -> Security -> users) permissions to the RSExecRole. How to do that is go the location I've mentioned in this section. Double click on the NTUser, and on Owned Schemas and Database role members, tick the RSExecRole (on owned schemas and database role membership).

    3.2) Now I had a database user that was mapped to my datasource in reporting services. I.E if I startup my report service website, go to my datasources, I checked the "credentials stored securely..." and ideally used a sql login. So effectively I would've needed to give that user RSExecrole rights. 

    Thus, back to sqlmanagementstudio, I went to server-> security -> logins -> MyRSSQLUser. Double clicked him and went to user mappings. I then selected the master and msdb tables, and the default schema was set to RSExecRole. I then needed to go back to the master and msdb db's. Go to Security -> Users. Get my MyRSSQLUser, and double click the user and on Owned Schemas and Role Members, selected RSExecRole.

     

    Hope this proves useful.

     

    Regards,

    Tuesday, September 15, 2009 11:10 AM
  • Full list of all permissions required are in this article on MSDN:

    http://msdn.microsoft.com/en-us/library/cc281308.aspx

    Thursday, May 27, 2010 2:30 PM
  • Hi All,

    I had the same problem. I had to migrate just the Team Foundation database server to a new server. It was after a few weeks that we got a request from a client to create a new team project and thats when I got this error. The error message is exactly the same as mentioned in the beginning of the post. I fwd this post to our DBA . He verified that the RSExecRole was already in both databases  (Master and MSDB). He granted the required execute permissons as mentioned above and that fixed my problem.

     

    Thanks,

    Ibheri

     

     

    Thursday, May 27, 2010 8:32 PM