Adding new custom entity to CRM 3.0 fails

Published 16/01/2008 by Henry in CRM
Tags:

While we went in production a few requirements were added to our CRM 3.0 project. The last thing we did before going into production was adding and removing roles and teams.

All system roles accept System Administrator where removed, only custom roles where needed, als teams where added. Because of the new features a new (custom) entity needed to be created. When I saved it the following message appeared:
"An error has occurred. For more information, contact your administrator" (talking about a meaningfull error message!).
While I tried to solve this problem I found out Microsoft has a hotfix available for this problem (http://support.microsoft.com/kb/936204/EN-US/). This hotfix can only be obtained through a support ticket from MS Support. In this blogpost is stated that the deletion of the 'System Customizer Role' does the damage.

It has a reference to this KB Article (Microsoft KB article 934690):
When a custom entity is created, Microsoft Dynamics CRM automatically grants the System Customizer role access to the new entity. If you delete or edit the System Customizer role, you receive the error message that is mentioned in the "Symptoms" section.
The blogpost states it appears that when creating a custom entity CRM checks if the 'System Customizer' role is present (at least the if a role with a RoleID equal to the Guid this role had when CRM was installed). Also just manually recreating a "System Customizer" role does not fix the problem.

The solutions present at that moment in time:

  • MS Hotfix
  • Fresh install of CRM with data migration (back up CRM, uninstall CRM, install CRM fresh and then migrate all of your data from the back up into the new system)

Now I really wanted to know what's going on, so I hooked up a trace with SQL Server's profiler. A lot of stored proc calls are made, but just before the first ROLLBACK this dynamic SQL was executed:

   1:  exec sp_executesql N ' select role.RoleId as ''roleid'',role.BusinessUnitId as ''businessunitid''
   2:  from Role as role inner join BusinessUnit as rolebusinessunitid
   3:  on (role.BusinessUnitId = rolebusinessunitid.BusinessUnitId) and rolebusinessunitid.DeletionStateCode in (0)
   4:  and (rolebusinessunitid.ParentBusinessUnitId is null )
   5:  inner join SystemUser as businessunitorganizationid
   6:  on (rolebusinessunitid.OrganizationId = businessunitorganizationid.OrganizationId)
   7:  and businessunitorganizationid.DeletionStateCode in (0)
   8:  and (businessunitorganizationid.SystemUserId = @P1)
   9:  where role.DeletionStateCode in (0)
  10:  and (role.RoleTemplateId = @P2)',N'@P1 uniqueidentifier,
  11:  @P2 uniqueidentifier','X1X1X1X1-Q1Q1-Q1Q1-Q1Q1-X1X1X1X1X1X1','Z2Z2Z2Z2-Y2Y2-Y2Y2-Y2Y2-Z2Z2Z2Z2Z2Z2'
Listing 1

When I ran it in the  SQL Server Manager's Query Window an empty row was returned. 
The following part of the SQL is interesting in this case:

(role.RoleTemplateId = @P2) 
Listing 2

Maybe this could be the Guid that is absent. When we look inside the RoleTemplateBase table we see that all system roles exist in this table, even though they do not exist as roles in this CRM implementation anymore.
When a new entity is created CRM checks to see if the executing user is a member of any role that has a relation to the template with the RoleTemplateId of the 'System Customizer' role template. This to me is strange behavior, specially because the RoleTemplateBase table has no field or relation to the settings of a Role, the only fields it has are:

  • RoleTemplateID;
  • Name;
  • Version;
  • Upgrade.

'RoleTemplateBase' table of the '<organization>_MSCRM' database  
'RoleTemplateBase' table of the '<organization>_MSCRM' database

I used the MS CRM Role Utility to export the System Customizer role to a xml file from a shadow VPC that still contained the role. Als with the MS CRM Role Utility I imported the role into CRM. Here is a link: Systeemaanpasser.xml (31,23 kb) to the xml containing the role (NOTE this is a Dutch CRM installation!, I will try to post an English version of the 'System Customizer' role also).
So the role is in  the system again, now I ran a somewhat customized version of the Dynamic SQL, so it is not dynamic anymore. Figuring when it returns a row this error will be gone.

Query:

   1:  select role.RoleId as 'roleid',role.BusinessUnitId as 'businessunitid' 
   2:  from Role as role 
   3:  inner join BusinessUnit as rolebusinessunitid 
   4:  on (role.BusinessUnitId = rolebusinessunitid.BusinessUnitId) 
   5:  and rolebusinessunitid.DeletionStateCode in (0) 
   6:  and (rolebusinessunitid.ParentBusinessUnitId is null ) 
   7:  inner join SystemUser as businessunitorganizationid 
   8:  on (rolebusinessunitid.OrganizationId = businessunitorganizationid.OrganizationId) 
   9:  and businessunitorganizationid.DeletionStateCode in (0)  
  10:  and (businessunitorganizationid.SystemUserId = 'X1X1X1X1-Q1Q1-Q1Q1-Q1Q1-X1X1X1X1X1X1')  
  11:  where role.DeletionStateCode in (0)  
  12:  and (role.RoleTemplateId = 'Z2Z2Z2Z2-Y2Y2-Y2Y2-Y2Y2-Z2Z2Z2Z2Z2Z2') 
Listing 3

I ran the query and it returned nothing:

Empty result of query
Empty result

I opened the 'RoleTemplateBase' table, selected the row containing the 'System Customizer' role and copied the Guid in the 'RoleTemplateID' field to the clipboard.
Than I opened the Role view (remember 'Select ... from Role as role') and searched for the row containing the newly added 'System Customizer' role.

Records in 'Role' view 
Records in 'Role' view

In this row I pasted the Guid (I earlier had copied to the clipboard) into the field 'TemplateRoleID' and saved it.
Than I ran the query again, but now it returned a row:

Result: "one row"
Result: "one row"

I tried to add a new custom entity into CRM and guess what it worked again!
So the advice I read in a few CRM related blogposts to never remove system roles (specially the 'System Customizer' role) is repeated here in this post.

Henry Cordes
My thoughts exactly...


Comments (6) -

David Brookes | Reply

04/09/2008 11:59:22 #

Just wanted to thank you for posting this fix, I've been right up sh!t creek since I deleted the role...

Your a star!

Cheers,
David

04/03/2009 03:27:01 #

Glad to be of service

Gary Allen | Reply

06/07/2009 19:05:49 #

Hi Henry, this is a great post and thanks for the info. I'm not a SQL expert but following your notes I managed to determine that I had this issue. The one piece I fail on is updating the Role View with my GUID from my newly re-added System Customizer role. Any chance you could provide a more detailed explanation (or actual SQL) so that I can make the required update?

Thanks

Gary

Gary Allen | Reply

06/07/2009 21:12:52 #

Hi Henry,

Nice post and very useful. I got stuck trying to update my Role view. Can you possible post the exact SQL to do this?

Thanks

Gary

harry | Reply

31/01/2010 07:46:55 #

great work!

Steven Rasmussen United States | Reply

01/11/2012 22:27:16 #

Thanks for posting this!!!  Just had this same issue in CRM 2011.  Restoring the 'System Customizer' role worked in my case.

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading