MS CRM list user access via SQL


During my CRM Solution import debugging yesterday, I also wanted to see which user had been logged in to CRM the last few days. After some googling og trying I came up with this SQL statement for listing all users and last time they accessed CRM during the last 3 days.

NB! You have to change the “OrgName” to get this working on you CRM database server. It is tested for CRM 2011 and CRM 2016.

USE MSCRM_Config
SELECT O.FriendlyName, SU.FullName as Name, SUO.LastAccessTime
FROM SystemUserOrganizations SUO
       LEFT JOIN SystemUserAuthentication SUA ON SUO.UserId = SUA.UserId 
                                    AND LEFT(AuthInfo, 1)='C'
       LEFT JOIN Organization O ON SUO.OrganizationId=O.Id
       INNER JOIN OrgName_MSCRM.dbo.SystemUser SU ON SUO.CrmUserId = SU.systemuserid
WHERE LastAccessTime IS NOT NULL 
  AND O.FriendlyName = 'OrgName' 
  AND datediff(DAY,Lastaccesstime, getutcdate()) < 3
ORDER BY lastaccesstime
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s