locked
The EXECUTE permission was denied on the object 'p_GetCrmUserId', database 'MSCRM_CONFIG', schema 'dbo'. RRS feed

  • Question

  • Hi all!

    We are facing a strange problem in 2 environments - one is our own and one is at a client site in a prod environment - in both cases the problem started after upgrading to UR12:

    error description:

    1.       Create new report using the report wizard (Inside the CRM)
    2.       Publish the report for external use 
    3.       Open reporting services manager web site
    4.       Open the report
    5.    We get the following error message:

    An error has occurred during report processing. (rsProcessingAborted)
    Cannot create a connection to data source 'CRM'. (rsErrorOpeningConnection)
    System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'p_GetCrmUserId', database 'MSCRM_CONFIG', schema 'dbo'.
    The EXECUTE permission was denied on the object 'p_GetCrmUserId', database 'MSCRM_CONFIG', schema 'dbo'.

    Any help would be appreciated!

    Thanks!

    Tuesday, February 19, 2013 3:46 PM

Answers

  • Hello, 

    While I don't know about your environment specifically and what may have caused this issue, have you tried:

    1. Open SQL management studio, connect to your instance hosting MSCRM databases

    2. Open MSCRM_CONFIG

    3. Open Programmability

    4. Open Stored Procedures

    5. Select DBO.p_GetCrmUserId, right click and open properties.

    6. In the Permissions tab, add the service account set in your SSRS options, and grant it the execute permission on the stored procedure.

    Best Wishes,

    Alex

    • Proposed as answer by AlexAquila Wednesday, February 20, 2013 7:37 PM
    • Marked as answer by Tzooki Thursday, February 21, 2013 8:11 AM
    Wednesday, February 20, 2013 7:36 PM

All replies

  • Having the identical issue on my end, started after upgrading to UR12 On-Premise

    Wednesday, February 20, 2013 12:42 AM
  • Hello, 

    While I don't know about your environment specifically and what may have caused this issue, have you tried:

    1. Open SQL management studio, connect to your instance hosting MSCRM databases

    2. Open MSCRM_CONFIG

    3. Open Programmability

    4. Open Stored Procedures

    5. Select DBO.p_GetCrmUserId, right click and open properties.

    6. In the Permissions tab, add the service account set in your SSRS options, and grant it the execute permission on the stored procedure.

    Best Wishes,

    Alex

    • Proposed as answer by AlexAquila Wednesday, February 20, 2013 7:37 PM
    • Marked as answer by Tzooki Thursday, February 21, 2013 8:11 AM
    Wednesday, February 20, 2013 7:36 PM
  • Thanks Alex - that solved it!

    Although i find it very strange - the SSRS are running under NETWORK SERVICE which already has permissions for MSCRM_CONFIG - and nothing changed besides the UR12 upgrade.

    I don't understand why all of a sudden it requires EXECUTE permissions - maybe they missed something in this rollup...

    Thanks for the help!

    Thursday, February 21, 2013 8:14 AM
  • This could be caused if the installer is doing a "DROP PROCEDURE" followed by a "CREATE PROCEDURE" during the installation, rather than an ALTER PROCEDURE. Not sure why that would be the case though, offhand. In any case, glad that resolved your issue :)
    Thursday, February 21, 2013 11:33 PM
  • Hi Alex,

    i am a little bit confused, if this change is a intentional change of the permissions by microsoft or not.

    The change of SQL-Permissions should be unsupported, so i am not realy sure if i should implement this at our customers environment, cause he has the same issue ...
    Have you seen any UR 12 Deployment where this permission was set without setting it manually?

    Kind Regards
    Thomas

    Thursday, March 21, 2013 1:22 PM
  • Thanks Alex , it solved my problem.
    Saturday, December 7, 2013 5:10 PM