Friday, April 20, 2018

No Microsoft Dynamics CRM user exists with the specified domain name and user ID


A Microsoft Dynamics CRM user record does not exist with the specified domain name and user ID.

Crm Exception: Message: No Microsoft Dynamics CRM user exists with the specified domain name and user ID, ErrorCode: -2147220652

 Error while retrieving userId. Exception: Microsoft.Crm.CrmSecurityException: Unable to find Active Directory object for SID
   at Microsoft.Crm.SecurityUtils.GetGuidFromSidLimitingSearches(String domain, Byte[] sid)
   at Microsoft.Crm.SecurityUtils.GetUserId(String domainName, Boolean limitGlobalCatalogSearches)

We were getting such error for only certain users after we migrated our CRM organization from CRM 2011 to CRM 2016.

Initial Investigation and results.
  • User was enabled.
  • User has all security roles assigned.
  • Even one of Service Account having System Administrative access was not able to login.

Understanding CRM Tables

There are four main tables where CRM user detail must exist for CRM authentication.

MSCRM_CONFIG.dbo.SystemUserOrganizations
MSCRM_CONFIG.dbo.SystemUserAuthentication
MSCRM_CONFIG.dbo.SystemUser
YourOrgName_MSCRM.dbo.SystemUserBase

Here is a query which should give an outcome with important details for a given CRM User.

select DomainName,ActiveDirectoryGuid,AuthInfo,A.UserId
from MSCRM_CONFIG.dbo.SystemUserOrganizations O
join MSCRM_CONFIG.dbo.SystemUserAuthentication A on A.UserId=O.UserId
join Staging.dbo.SystemUserBase B on B.SystemUserId=O.CrmUserId
Where B.DomainName like '%itsit\vipin.jaiswal%'


If you are not getting SQL Result, then it might be possible that there is missing entry for a CRM user in one of the table.

So, we need to verify in which table we are missing the respective crm user entry and verification must be done in specific order as described here.

First :
Please note SystemUserID of the respective user as this would be an input for our second query.

Select DomainName,SystemUserID
From Staging_MSCRM.dbo.SystemUserBase
Where DomainName like '%itsit\vipin.jaiswal%'



Second :
The SystemUserId from above is an input to below query in where clause.

Select CrmUserId,Id,OrganizationId,UserId,IsDeleted
From MSCRM_CONFIG.dbo.SystemUserOrganizations
Where CrmUserId = 'F2B56B91-CE43-E811-9103-005056A83905'



Note the UserId from above it will be an input to our Third and Fourth Query.

Third and Fourth :

Select AuthInfo,Id,UserId,IsDeleted
From MSCRM_CONFIG.dbo.SystemUserAuthentication
Where UserId = 'F9B56B91-CE43-E811-9103-005056A83905'

Select DefaultOrganizationId,Id,IsDeleted
From MSCRM_CONFIG.dbo.SystemUser
Where Id = 'F9B56B91-CE43-E811-9103-005056A83905'




In most cases we have got an entry in table YourOrgName_MSCRM.dbo.SystemUserBase and rest other 3 tables were missing the entry.

I have created a stored Procedure which will create missing entry in the respective table accordingly.

Disclaimer: As always, any direct changes in the CRM tables are unsupported.

Please Note:

SP require an Active Directory ID of  a CRM User and I know two ways to get it.

     1) Get the ID from your any other existing organization  from below query.
Select AuthInfo,Id,UserId,IsDeleted
From MSCRM_CONFIG.dbo.SystemUserAuthentication
Where UserId = @CrmUserConfigId

      2) Get the ID from running power shell command

Get-ADUser -Filter {SamAccountName -eq 'YourUserName'}



-------------Stored Procedure---------------


Create Procedure usp_CreateCrmUser
(@OrgName varchar(100), 
 @CrmUserDomainName varchar(100), 
 @CrmUserACtiveDirectoryID varchar(100))
As

/* 
----Run this Stored Procedure as

Exec CreateCrmUser 'Staging_MSCRM','itsit\Vipin.Jaiswal','W:S-1-5-21-1328376081-1279679187-339368940-342572' 
*/

Begin

Declare @OrganizationID uniqueidentifier
Declare @CrmUserConfigID uniqueidentifier
Declare @SystemUserID uniqueidentifier

Select @OrganizationID = Id
From MSCRM_Config.Dbo.Organization Where DatabaseName = @OrgName

Select @CrmUserConfigID = NEWID()

Select @SystemUserID = SystemUserId
From Staging_MSCRM.dbo.SystemUserBase Where DomainName = @CrmUserName

       If @OrganizationID IS NULL
       Begin
              Select [Output] = 'Organization Name  ''' + @OrgName + '''  does not exists'
              return
       End

       If @SystemUserID IS NULL
       Begin
       Select [Output] = 'User  ''' + @CrmUserName + '''  does not exists in Organization : '' ' + @OrgName + ''''
       return
       End
      
       Insert into MSCRM_CONFIG.dbo.[SystemUser] (DefaultOrganizationId,Id,IsDisabled,Name,IsDeleted)
       Values (@OrganizationID,@CrmUserConfigID,null,null,0)

       Insert into MSCRM_CONFIG.dbo.SystemUserOrganizations (CrmUserId, Id, OrganizationId, UserId, IsDeleted)
       Values (@SystemUserID, NEWID(), @OrganizationID, @CrmUserConfigID, 0)

       Insert into MSCRM_CONFIG.dbo.SystemUserAuthentication (AuthInfo,Id,UserId,IsDeleted)
       Values (@CrmUserACtiveDirectoryID, NEWID(), @CrmUserConfigID, 0)    
             
    Select [Output] = 'User  ''' + @CrmUserName + '''  Created for Organization : '' ' + @OrgName + ''''

       Select DomainName,ActiveDirectoryGuid,AuthInfo,A.UserId
       From MSCRM_CONFIG.dbo.SystemUserOrganizations O
       join MSCRM_CONFIG.dbo.SystemUserAuthentication A on A.UserId=O.UserId
       join Staging_MSCRM.dbo.SystemUserBase B on B.SystemUserId=O.CrmUserId
       Where B.DomainName = @CrmUserName

End

Invoking a stored procedure entered the appropriate missing entry in SQL table and users were able to log-on.

Thanks,
Vipin Jaiswal
vipinjaiswal12@gmail.com


1 comment:

Evon Crooks Jr said...

Greatly appreciate this script but currently having an issue with SQL query error of:
Must declare the scalar variable "@CrmUserName".