Problem

I recently encountered a strange issue in our Microsoft Dynamics CRM 4.0 environment. We could not ENABLE a user that had previously been disabled, instead we encountered the following error.

CRM Enable User Screenshot

CRM Error

 

Analysis

Monitoring the CRM IIS web server process (W3WP.exe) with the Sysinternals ProcDump analysis tool showed the following unique constraint database errors occurring.

Sysinternals ProcDump Monitor

 

Solution

Remove the duplicate rows in the dbo.SystemUserPrincipals database table.

I used the following SQL script.

 --
 -- SELECT SystemUserId
 -- FROM systemuserbase
 -- WHERE Fullname like '%Tompkins%'
 --
 -- Returns A0BAE3A7-6B76-DE11-A727-005056B44698
 --
 

USE HRPAYS_MSCRM
GO

BEGIN TRANSACTION

BEGIN TRY

DELETE FROM SystemUserPrincipals
WHERE SystemUserId IN (SELECT systemuserid
FROM systemuserbase
WHERE isdisabled = 1
AND SystemUserId = 'A0BAE3A7-6B76-DE11-A727-005056B44698')

PRINT N'Committing transaction';

COMMIT TRANSACTION;
END TRY

BEGIN CATCH
DECLARE @ERR_MESSAGE nvarchar(1000);
SET @ERR_MESSAGE = (SELECT CAST(ERROR_NUMBER() as NVARCHAR) + N' ... ' +
CAST(ERROR_MESSAGE() as NVARCHAR) + N' ... ' +
CAST(ERROR_LINE() as NVARCHAR));

PRINT @ERR_MESSAGE;
ROLLBACK TRANSACTION;
PRINT N'An error occurred ...';
END CATCH

GO
Shane Bartholomeusz
Latest posts by Shane Bartholomeusz (see all)