CRM 2013 and SQL Reports - how do I connect the two so fn_FindUserGuid will function? RRS feed

  • Question

  • I'm creating some SQL Server reports in SQL Server Data Tools (Visual Studio 2010) and I've installed the CRM 2013 Report Authoring Extension.  When I create a Data Source pointing back to my CRM database and create a report based on fn_FindUserGuid it just returns the CRM Service ACcount instead of the logged in CRM user account.  I even tested SUSER_SNAME() which the function uses, but this just returns the service account as expected.

    So what connection changes do I need to make in my RDL report connection string back to CRM to get fn_FindUserGuid to work for reports using SQL (not FetchXML)?

    Thanks --

    Thursday, April 9, 2015 4:23 PM

All replies

  • I am pretty sure that you will only get back that one user account.  The CRM Data Extension creates a custom connection to SSRS using the same single account through impersonation when a user attempts to run a report.  This is why only a single license is required for the reports piece of CRM.

    The users' credentials for the one running the report are also passed in the connection, but I'm thinking that SSRS only really only uses the user credentials to help filter the data that is returned through the filtered views.  Not as an actual authentication to SSRS.

    So you are likely not going to easily going to get a stored function in SQL to return the real user running the report.

    Jason Peterson

    Thursday, April 9, 2015 8:18 PM
  • I use this method, code below: http://crmtipoftheday.com/2014/01/09/reflecting-current-user-ssrs/

    select fsu.systemuserid
    from FilteredSystemUser fsu
    where systemuserid = dbo.fn_FindUserGuid()

    I'm using 2011, but I assume the same applies. 

    I made a DataSet using this query, then point a hidden parameter to this dataset, then pass the parameter to the sproc, and use SET CONTEXT_INFO to set it to the guid of the user.  This is what CRM uses for it's filtered views....

    I use Report Builder to layout the reports, but I assume it's the same as Visual Studio, and when I run the report in Report Builder the function doesn't return anything so it passes a blank. Therefore I have to hardcode a User ID in the sproc when the parameter is blank...

    Hopefully this is a help..

    Friday, April 10, 2015 8:01 AM