MS-CRM - Query to list the Activity record distribution across Business Units in the CRM database

The MS-CRM contains many entities of type Activity. Few of the most widely used activities are PhoneCall, Email, Appointment etc..

When the activity record is created in MS-CRM, it internally loads 3 tables with the provided information

1. Base table for the Activity. Ex. - AppointmentBase
2. ActivityPointerBase
3. ActivityPartyBase

ActivityPointerBase table contains one record for each activity record that is created in the MS-CRM. While ActivityPartyBase table hols the information of the parties (participants) for particular Activity record.

Following tables are used in the query to find the distribution of the activities across 

MS-CRM - Query to list the TimeZones for the Active Users

In MS-CRM database as we know end user can set the timezone of his choice for himself / herself. And after that all the data that end user sees on CRM UI would be displayed in the timezone that he/she has opted for. 

Now, the question is where does CRM stores the TIMEZONE related information? hence comes the TimeZoneDefinitionBase table to rescue. 

It holds the info such as 

• TimeZoneCode - Unique Integer value representing each timezone
• UserInterfaceName - Displays info in-terms of UTC +/- Hrs
• StatndardName - Gives away the global standard name for the TimeZone that everyone can understand

MS-CRM - Query to list the Roles which are in use

I'm going to keep this blog post short and simple.. 

In case you are working on the MS-CRM and came across a situation wherein you want to list the roles that are currently in use in the particular organization then following query would help you out.

Objects used in the query are..

• SystemUserRoles - Holds the association between users of the CRM organization and the roles from it
• RoleBase - Lists the roles across CRM organization
• SystemUserbase - list of users present in the CRM system. Isdisabled column from the table tells us whether user is in enabled or disabled state

Please run this query against your CRM database. I am assuming here that you have READ privileges on the CRM database.

MS-CRM - Query to list the Access Rights of the Role's on all the Entities

Recently, one of the Business Analyst associated with the CRM project wanted to know the access rights for all the roles across all the entities using T-SQL? The MS-CRM (4.0) database for which end user intended to fetch this information did not have any organization created on the top of it. Thus end users were not able to know what roles has what permission and on what entity...

I thought it would be good exercise to write a query to fetch this information. So I started writing one and at the end had following query ready with me.. It fetches required information for the roles which are associated with the Active users only. This means the roles which are not in use (or part of Inactive user) would not feature in the output of the query

Database Objects used in the query are listed below

• PrivilegeObjectTypeCodes - Contains the association between PrivilegeId and ObjectTypeCode of the entity
• PrivilegeBase - Contains the information about privileges such as Name, GUID etc..
• RolePrivileges -  Association between Roles and Privileges is defined here 
• RoleBase - Contains the information about Roles such as Name, GUID etc..
• EntityView - Has the information about Entities
• SystemUserRoles -  Association between Users and Roles
• SystemUser - Information about CRM users

MS-CRM - Query to List Roles assigned to Active users

This requirement came when I was working on the MS-CRM migration project and my client wanted to know ROLE's that ACTIVE users are part of....

In MS-CRM following objects hold this information

• SystemUserBase - Contains User related information. It also has a Isdisabled column which gives the information about which one's are active and which one's are not

• RoleBase - List of roles that are defined in the MS-CRM Organization

• SystemUserRole - defines the relationship between user and the role.

Following query will give fetch us the roles and the count of active users belonging to that role.

MS-CRM - Query to list the roles that user belongs to in a business unit

One day my client asked me to give him the list of CRM users and their roles as defined in the CRM organization. He also laid down couple of conditions 

• If a user belongs to more than one role then roles must be displayed as a comma separated string in a single row.
• We must know whether that user is in disabled state or not
• What is the business unit of the User

Luckily for me all this information was store in 3 objetcs (Table / view)

• SystemUser
• SystemUserRole
• Role

So given below is what I came up with

MS-CRM - Query to list the relationship between the Entities

In the recent past, I have been associated with multiple data migration projects. All of them had one thing common, the destination for all those projects was MS-CRM database. 

There were occasions when it was required to know the parent/child entity relationship before SSIS package for the migration could be designed. For this information, I used to bug CRM team of respective project every now and then, thanks to my exceptional remembering/recalling power and unfamiliarity with CRM Tool . :) 

CRM Team had enough of me one day and told me "So what you don't know CRM tool, entity relationship information can be extracted from CRM database as well." A
And it triggered me to write this script and believe me CRM Team was more happy than I was when I was done with it..