Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
How to reference current CRM user from a SQL based SSRS Report that's called from CRM? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello.  I'm building some reports (RDL) that are using SQL to retrieve the data because the queries are too complex for FetchXML, but I'm unable to find a way for the SQL report to know which CRM user is calling the report.  The report is always called by the CRM Service account so SQL functions like CURRENT_USER will not work.  Also I read one article that suggested using dbo.fn_FindUserGuid(), but this also just queries the current SQL user which is the service account.

    I hoped I could add a hidden parameter and pass the User's GUID to the report from CRM to the report, but I haven't found any way to do this.  Now is this possible?  

    Thanks.  Also this is in CRM 2013 SP1 (rollup 2) on Premises.

    Monday, March 30, 2015 3:03 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    In testing I've created a report that has two Datasets, one uses FetchXML to get the current user GUID and the other a SQL dataset with the query I need to run.  I'm pulling the current user's GUID from CRM using the FetchXML dataset, saving this to a parameter, then passing this into the SQL Dataset.  This seems to function in Visual Studio, but when I deploy this to CRM it fails to import.  Can CRM not handle Reports with both FetchXML and SQL datasets?  If so this would fix my issue.

    Thanks.

    Monday, March 30, 2015 6:52 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Sam,

    crm reports are supposed to be run with current user credentials as opposed to sql server account. I have used dbo.fn_FindUserGuid on few reports and never had any issues.

    please check on your ssrs folder if the reports are set to run with stored credentials or with the current user credentials.

    running reports with sql server credentials will allow users to see data that they are not supposed to see.

    is it possible that dbo.fn_FindUserGuid is returning wrong id because you are testing the report directly?.

    regards

    Jithesh

    Monday, March 30, 2015 10:32 PM