How To Find Default Organization For Any User In Multiple Organization CRM Deployment


 

-Log in to SQL Server where we have CRM databases <org>_MSCRM and MSCRM_CONFIG. We have 4 different tables from where we will have to fetch the user entries.

 

-SystemUserBase,

-SystemUserOrganizations,

-SystemUser and then

-Organization

 

Refer to the following diagram which will help you understand how different tables are related for a specific user.

 

 

 

 

 

Run following queries one by one and fetch the GUID values as follows:-

 

Run below query on ORG_MSCRM database :-

 

Select domainname,systemuserid from systemuserbase where domainname =’domainname\username’

 

Note:-

domainname\username is for the affected user.

 

Copy SystemUserID = 7F0F58A8-5ED0-E111-B576-00155D5AC438

 

 

 

 

Run following query in MSCRM_CONFIG database :-

 

USE [MSCRM_CONFIG]

select UserId from systemuserorganizations where CrmUserId=’7F0F58A8-5ED0-E111-B576-00155D5AC438′

 

Copy UserId = D058E3E4-EF63-E111-97DA-00155D5AC42A

 

 

 

  USE [MSCRM_CONFIG]

select defaultOrganizationId from SystemUser where id=’D058E3E4-EF63-E111-97DA-00155D5AC42A’

 

Copy default org id= 076DC913-4564-4569-9A8C-9CD1C4A4ABA2

 

  

 

USE [MSCRM_CONFIG]

select DatabaseName,UniqueName from organization where id = ‘076DC913-4564-4569-9A8C-9CD1C4A4ABA2’

 

 

Default Org XXX_MSCRM

 

This way we can find the default organization for any user.


Comments (5)

  1. Anonymous says:

    That's a lot of work to just do this:

    Select a.domainname

    ,a.systemuserid
    
    ,d.DatabaseName
    
    ,d.uniquename 
    

    from <Org Name>_MSCRM.dbo.systemuserbase a WITH (NOLOCK)

    inner join MSCRM_CONFIG.dbo.SystemUserOrganizations b WITH (NOLOCK)

    on a.SystemUserId = b.CRMUserId
    

    inner join MSCRM_CONFIG.dbo.SystemUser c WITH (NOLOCK)

    on b.UserId = c.id
    

    inner join MSCRM_CONFIG.dbo.Organization d WITH (NOLOCK)

    on c.DefaultOrganizationId = d.id
    

    where a.domainname ='DomainUsername'

  2. Anonymous says:

    Thanks Susan. Yes, this query will directly fetch the default org for a user and save your time but if someone wants to understand the concept how user records are related in different table they should know the concept.

  3. Anonymous says:

    Very helpful!

    I was able to use this to manually change the default org for users at the DB level. Do you know how long it takes for any changes to take effect?

    I noticed the change didn't take immediately, but later in the day everything was fine. I was wondering if the app pools needed recycled or something to that effect.

  4. Anonymous says:

    Hi Joe,

    I'm glad this article helped you. I wanted to add that changing default org of a user from database directly is not supported even. In fact any direct changes it database is not supported. This does not mean that what you did is wrong or wont work 🙂

    Usually database changes do not require App pool recycle but may be if CRM was using cached values from IE/ browser then you might see this scenario what you saw.

    Thanks,

    Arpita

  5. Anonymous says:

    I had no option to delete the organization which is in disabled status.

    So,I was able to use these queries to manually change the default organization for users at the DB level.

    Also the changes became effective immediately.