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.

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
  AND O.FriendlyName = 'OrgName' 
  AND datediff(DAY,Lastaccesstime, getutcdate()) < 3
ORDER BY lastaccesstime

Dynamcis CRM 2011: reinstalling server from RTM to Rollup 10 v2

Yesterday afternoon I had to start a full re-installation of a CRM 2011 server due to missing product key to upgrade from trail version to production server license.  The trail server had installed rollup 10 v2 with English database language and Norwegian language package. The installation media provided by the customer was the RTM version, and I need to install this and first install the Rollup 6 before applying the Rollup 10 v2. When this was completed, I could reimport the old organization database.

Dynamics CRM in the Field” team has a good post with compact information with the different rollup and service updated, build number, release dates and KB articles.

In addition, Pablo Peralta has good step-by-step post on how to install a CRM 2011 server On-premise.

CRM 2011: upgrade from trail product key

Yesterday, I encountered a new issue for a customer. They received an error message stated that the trail key was to expire in a few days. “WTF!!!” was my first thought, but it seems this is a common scenario according to this post.

You will recieve a similar error message:


The most important thing to remember. Do not let the trail key expire. If you do so, you will have to re-install the complete server and import the organization one more time. This will cost about one workday alone.

The Product key can be changes from the “CRM deployment Manager”.


Migrating CRM 4 to CRM 2011 – Part 1

This is my second blog in the series of moving from CRM 4 to CRM 2011 in different scenarios. My first blog described the alternative of move a CRM 4 installation to new SQL Server 2012. After some consideration, the customer has decided to update the CRM 4 solution to CRM 2011 instead of just moving to a new SQL Server. The actual move to the new SQL Server 2012 involved a additional cost since the CRM 4 had to be updated with latest Rollup 21 to be compatible with SQL Server 2012.

After some googling and reading through the quite large, unreadable, exhausting to read. Yes, you get the information you need, but I can’t understand why Microsoft can’t write better installation guides. They surely have the man power.

Enough whining…

After some consideration, I decided to do the following:

  • Install a new CRM 2011 server and use the new SQL 2012 server
  • Take a backup of the organisation database on the old SQL 2005 server
  • Use CRM 2011 Deployment Manager to disable/delete the current “dummy” organsation
  • Restore CRM 4 organisation database (from SQL 2005) on the new SQL 2012 server
  • Use CRM 2011 Deployment Manager to import the CRM 4 organisation database

Just a small guide. I found this blog from Pablo, that helped me alot during the “read-up phase” for this migrating project.


CRM 2011: Invoice status

Here is a useful SQL statement for quickly find the invoice status based on Invoice.StateCode and Invoice.StatusCode. The SELECT will list all invoices for year 2012 where StateCode and StatusCode as both numbers (stored in CRM-database) in addition to the textual description.

	month(createdon) as monthNo, 
		WHEN StateCode = '0' THEN 'Active'
		WHEN StateCode = '2' THEN 'Paid'
		WHEN StateCode = '3' THEN 'Cancelled'
	 END) as StateText, 
		WHEN StatusCode = '1' THEN 'New'
		WHEN StatusCode = '4' THEN 'Billed'
		WHEN StatusCode = '100001' THEN 'Complete'
		WHEN StatusCode = '100002' THEN 'Parial'
		WHEN StatusCode = '100003' THEN 'Canceled'
	) as StatusText,
	count([TotalAmount]) as InvoiceCount
FROM [dbo].[Invoice]
WHERE year(createdon) = 2012
group by month(createdon), StateCode, StatusCode
order by  month(createdon),StateCode, StatusCode ;

The above SQL returns invoice count grouped by the different statuses. An example of the outbut is shown in the screenshot below: