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