How to get the identity of the user who ran a report?

Question

When executing a report in MSCRM 2011, you have two options for the data sources : either SQL or Fetch.

In SSRS execution logs, the user who ran the report is always the service account.

When using fetch, in the report execution log, there is a parameter CRM_FullName containing the full name of the user who ran the report.

When using SQL source, there is no CRM_FullName parameter. How can I know who ran the report? There must be a way to know, since the Filtered views know who I am.

Dewees

Answer 1

There is actually no way to find this information. When you create a report for MSCRM, you use a connector called "MSCRM Data Connector". This can be seen in the AdditionnalInfo column of the ExecutionLogs3 view on the SSRS instance. When using this connector and trying to show a report, you will get prompted for username and password. That's where things get interesting.

The report is not actually expecting a username/password! In fact, it expects to receive the systemuserid (guid) as username and the organizationid (guid) as password. It then search in the MSCRM_CONFIG database for the organization database settings. Then, it goes into the organization database and simply do a set context_info SYSTEMUSERID. Finally, the filteredviews are calling a function named '[dbo].[fn_FindUserGuid]' that retrieves the context_info. That is how the filtered views are properly working, while connected as the service account.

As you might have expected, we can't know the user who ran the report because the username and password prompts in SSRS are never logged anywhere (for security matters, perhaps).

The best option that I have found to log who ran a report is to actually create a stored procedure that will make a select statement on the filtered views (or any tables, as it is) and then log into a separate table the statement, the procedure parameters and the context_info(). Then, in SSRS I call that function instead of going to the filtered views directly.

Dewees

Answer 2

Answer edited to include the getting the user fullname if sql source is used.

1. Register a parameter, UserID, with default value as

   =User!UserID

2. Use the following query in your dataset

   SELECT DomainName, FullName

   FROM SystemUserBase

   WHERE (DomainName = @UserID)

3. Then use

   =Fields!FullName.Value

in your report.

In the CRM the user data is stored in the SystemUserBase table, and the DomainName column is the actual Domain\Username stored in User!UserID of the report. If you prefer using views, use FilteredSystemUser view instead of SystemUserBase table.

For fetchxml try the following: The operator operator='eq-userid' means equal to the current user.

<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
<entity name='team'>
<attribute name='name' />
<attribute name='businessunitid' />
<attribute name='teamid' />
<order attribute='name' descending='false' />
<link-entity name='teammembership' from='teamid' to='teamid' visible='false' intersect='true'>
<link-entity name='systemuser' from='systemuserid' to='systemuserid' alias='user'>
    <attribute name='fullname' />
    <attribute name='systemuserid'/>
        <filter type='and'>
        <condition attribute='systemuserid' operator='eq-userid' />
        </filter>
</link-entity>
</link-entity>
</entity>
</fetch>

Then in your report you can use the following code in an expression to get the users fullname

=First(Fields!user_fullname.Value, "GetUserData")

where the dataset is called GetUserData

Florentinoflorenza

The question was about using SQL Source. As stated in the question, I know that it is logged when using FetchXML. – Dewees

Yes you are correct, i have edited the answer to include fetching of username if sql source was used. – Florentinoflorenza

Your answer (using User!UserID) will display the username in the report, but it will not be stored in the execution logs. My question originally asked how to retrieve that info in the execution log. Thanks though for the time invested. – Dewees

Answer 3

Will the user login name suffice? i.e. can you just use something like ="Generated by " & User!UserID ?

Saros