Friday, September 4, 2009

Default Organization For User is incorrect in CRM 4.0

Occasionally you come across the situation where the default organization has been changed and some of the users are still mapped with the default to the old database. This is quite annoying and can actually point your custom aspx pages to the wrong organization.

To fix this issue, I've written a SQL Server script which basically does the following:

  1. Finds the specified organization in the MSCRM_CONFIG database (the one that you want to be the default)
  2. Finds the specified user ( which has the incorrect default database ) in the organization database which is intended to be the default (Based on '<domain>\<username>')
  3. Finds the user details in the MSCRM_CONFIG database from the specified user found in the organization database
  4. Updates the user record in the MSCRM_CONFIG database to have the specified default organization set.

Notes on running the script:

  1. Backup your MSCRM_CONFIG database first! This is unsupported by Microsoft and Me!
  2. Set the Domain\Username & Organization Name in the section titled 'PLEASE FILL IN THE FOLLOWING'
    1. The @UserDomainName is the same as found the DomainName box in the User CRM Screen
    2. The @DefaultOrganization should be the name of the organization as shown in the deployment manager
  3. The script is designed to be executed in the context of the organization database which you want to be the default for the specified user, please make sure that this is set to the correct database before running
    1. i.e. you can do this by using the 'Use <databasename>' command in SQL first before executing the script.
  4. UPDATE: You may need to restart IIS in order for the change to take place.





DECLARE @DefaultOrganization AS VARCHAR (100);

DECLARE @DefaultOrganizationId AS VARCHAR (100);

DECLARE @UserDomainName AS VARCHAR (100);

DECLARE @CRMUserId AS VARCHAR (100);

DECLARE @UserId AS VARCHAR (100);

SET @UserDomainName = '<domain>\<username>';

SET @DefaultOrganization = '<organizationname>';

SELECT @DefaultOrganizationId = id
FROM MSCRM_CONFIG..organization
WHERE UniqueName = @DefaultOrganization;

IF @DefaultOrganizationId IS NOT NULL
BEGIN
SELECT @CRMUserId = systemuserid
FROM systemuserbase
WHERE domainname = @UserDomainName;
IF @CRMUserId IS NOT NULL
BEGIN
SELECT @UserId = userid
FROM MSCRM_CONFIG..systemuserorganizations
WHERE crmuserid = @CRMUserId;
IF @UserId IS NOT NULL
BEGIN
SELECT 'BEFORE', *
FROM MSCRM_CONFIG..systemuser
WHERE id = @UserId;
UPDATE MSCRM_CONFIG..systemuser
SET defaultorganizationid = @DefaultOrganizationId
WHERE id = @UserId;
SELECT 'AFTER', *
FROM MSCRM_CONFIG..systemuser
WHERE id = @UserId;
END
ELSE
BEGIN
PRINT 'The User was Not Found in the Organization - Please check';
END
END
ELSE
BEGIN
PRINT 'The User was Not Found in CRM - Please check';
END
END
ELSE
BEGIN
PRINT 'Default Organization Not Found - Please check';
END

4 comments:

billakc said...

Thanks!!! I've been looking for an answer to this for some time now.

I was having problems with custom aspx pages in our development environment that uses multi tenant. I figured out the problem was the incorrect default org, but couldn't seem to find any info on where this was determined. Thanks again.

Unknown said...

Hi Matt,

Great work, thanks for sharing the SQL. with the multi-tenant instance I am working on these days, I had to delete an organisation and re-create it under the SAME NAME. When I run your script, it made the deleted organisation default. The CONFIG database remembers this and has a IsDeleted flag set to 1. I have successfully executed the script with a little modification that checks for this flag to set the default organisation.

1. As I have only one organisation (even on multi-tenant instance), I could set the same org for every CONFIG system user.

2. IIS RESET IS required.


Here is the later part,
-------------------
-- NOTE: Don't touch the below statements
-- does the organisation exist?
SELECT @DefaultOrganizationId = id FROM MSCRM_CONFIG..organization WHERE UniqueName = @DefaultOrganization AND IsDeleted = 0
IF @DefaultOrganizationId IS NOT NULL
BEGIN
--does the user exist?
SELECT @CRMUserId = systemuserid FROM systemuserbase WHERE domainname = @UserDomainName
IF @CRMUserId IS NOT NULL
BEGIN
-- does CONFIG know about this user?
SELECT @UserId = userid FROM MSCRM_CONFIG..systemuserorganizations WHERE crmuserid = @CRMUserId
IF @UserId IS NOT NULL
BEGIN
-- Change the default org in CONFIG for that user
SELECT 'BEFORE', * FROM MSCRM_CONFIG..systemuser WHERE id = @UserId
UPDATE MSCRM_CONFIG..systemuser
SET defaultorganizationid = @DefaultOrganizationId
-- Comment following line out if you want the script to allow all the users TODO Check if the user exists? not needed?
WHERE id = @UserId
SELECT 'AFTER', * FROM MSCRM_CONFIG..systemuser WHERE id = @UserId
END
ELSE
BEGIN
PRINT 'The User was Not Found in the Organization - Please check'
END
END
ELSE
BEGIN
PRINT 'The User was Not Found in CRM - Please check'
END
END
ELSE
BEGIN
PRINT 'Default Organization Not Found - Please check'
END
-------------------

Matthew Hunter said...

Thanks Mistery Man!

Pano Kappos said...

Thank you sire!

Took us all morning to figure this out but we were a bit nervous of our proposed solution.

Finding your blog entry gives me alot more confidence in the unsupported hack required.