locked
crm report permission denied, why? RRS feed

  • Question

  • Hi!

    I have a problem with our crm. We have two crm, a test and which use the sales.
    In test crm i make a report and everything work fine.
    I upload it on other crm and a get this message when i want to run it:
    # An error has occurred during report processing.
    * Query execution failed for data set 'DSBigSum'.
    The SELECT permission was denied on the object 'ProductExtensionBase', database 'Isys_On_Kft__MSCRM', schema 'dbo'.
    My question is where can i give permission to this?
    thx all!
    If you dont understand something please write me.

    Friday, June 25, 2010 12:05 PM

Answers

  • To clarify the points from the above replies: The supported (and recommended) approach is to only query the filtered views (e.g. FilteredProduct), and not the underyling SQL tables. All CRM users will be granted SQL select permission on the filtered views, but are not granted any other SQL permissions
    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    • Marked as answer by bandolir Monday, June 28, 2010 12:56 PM
    Friday, June 25, 2010 12:54 PM
    Moderator

All replies

  • Do you have appropriate permissions on Product entity ???
    Mahain
    Friday, June 25, 2010 12:19 PM
    Moderator
  • Hi bandolir,

                 You can give permission in properties of table. You need open SQL management studio, after this  open the database 'Isys_On_Kft__MSCRM' and go 'Tables -> ProductExtensionBase'

    Friday, June 25, 2010 12:23 PM
  • Hi,

    Are you refering tables in your report ?? If yes then you should not refer tables directly instead you have to use filtered view to build your report.


    Mahain
    Friday, June 25, 2010 12:32 PM
    Moderator
  • To clarify the points from the above replies: The supported (and recommended) approach is to only query the filtered views (e.g. FilteredProduct), and not the underyling SQL tables. All CRM users will be granted SQL select permission on the filtered views, but are not granted any other SQL permissions
    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    • Marked as answer by bandolir Monday, June 28, 2010 12:56 PM
    Friday, June 25, 2010 12:54 PM
    Moderator
  • Thank you David
    Mahain
    Friday, June 25, 2010 1:09 PM
    Moderator
  • thx i make it and this problem solved but now i have this:

  • An error has occurred during report processing.
  • Query execution failed for data set 'DSSum5HUF'.
  • For more information about this error navigate to the report server on the local server machine, or enable remote errors


  • On server everything is ok the report run ok

  • but when a user run this report this message which i see
  • the query is this:
    DECLARE @sql AS nVarchar(max)
    SET              @sql = '
    SELECT   
    SUM(QuoteDet1.new_listardevizban * QuoteDet1.quantity) AS SumArbevetDev
    FROM        FilteredQuote  AS Quote1
    INNER JOIN FilteredQuoteDetail  AS QuoteDet1 ON Quote1.quoteid = QuoteDet1.quoteid
    INNER JOIN FilteredProduct AS Product1 ON QuoteDet1.productid = Product1.productid
    INNER JOIN (' + @CRM_FilteredAccount + ') AS Account1 ON Quote1.accountid = Account1.AccountId
    INNER JOIN StringMap AS StringMap_1 ON QuoteDet1.new_rdevizja = StringMap_1.AttributeValue
    INNER JOIN  (' + @CRM_FilteredOpportunity  + ')  AS Opportunity ON  Account1.AccountId = Opportunity.AccountId AND Quote1.OpportunityId = Opportunity.OpportunityId
    WHERE Product1.producttypecode = 5 AND StringMap_1.AttributeName = N''New_rdevizja'' AND StringMap_1.Value =N''HUF''
    AND quote1.RevisionNumber =
     (select max( temp1.RevisionNumber)
     from (
    SELECT    
    Product1.name,
    quote1.RevisionNumber
    FROM        FilteredQuote  AS Quote1
    INNER JOIN FilteredQuoteDetail  AS QuoteDet1 ON Quote1.quoteid = QuoteDet1.quoteid
    INNER JOIN FilteredProduct AS Product1 ON QuoteDet1.productid = Product1.productid
    INNER JOIN ('  + @CRM_FilteredAccount + ') AS Account1 ON Quote1.accountid = Account1.AccountId
    INNER JOIN (' + @CRM_FilteredOpportunity  + ')  AS Opportunity ON  Account1.AccountId = Opportunity.AccountId AND Quote1.OpportunityId = Opportunity.OpportunityId
    WHERE Product1.producttypecode = 5
    ) as temp1 where temp1.name = product1.name)
    '
                           EXEC (@sql)


  • what could be the problem is?
    thx all!

Monday, June 28, 2010 11:35 AM