How to Secure Your Data with Dynamics CRM

In this day and age, enterprises have mountains of data lying around (in data warehouses, data marts, and plain old legacy LOB systems), and securing that data is a key concern. This can involve giving people access to a database or api and letting them query away, but it can also involve allowing end users to see only the slices of data for which they naturally have access. For example:

  • Letting a sales associate working in Western Europe see only the sales data from their own geographic location.
  • Assigning a default geography showing in the sales dashboard for account managers with accounts in multiple sales geographies.

Now these might sound like simple things that Dynamics CRM should be able to do out of the box with teams, row ownership, etc., and you would be right, if all of the data you are reporting off of transactional data resides in CRM. But what happens if all the data you are reporting off of meets one of these typical organizational scenarios?

  • The data resides in an old LOB system that is not easily customized or supported.
  • You have a data warehouse that contains hundreds of millions of records, which makes importing it into Dynamics infeasible.
  • You are reporting from a system that another department/group owns, and your access is strictly read-only.

Luckily, you don’t need to import all of your data into Dynamics to enjoy the security benefits that Dynamics can bring to the table. I will present a solution for the aforementioned reporting scenarios.

Limit Data to a User Region(s)
First, if it’s not already in Dynamics CRM, create a new entity that represents your specific geography. Be sure to include an ‘external_id’ column, which represents the natural key of your entity, and represents the link back to your source system.

Then, use your favorite integration tool (Scribe, SSIS, .NET Console Application) and import this data into Dynamics.

Next, customize your user entity and add a new relationship (Lookup, 1:M, M:M, depending on your need) to your new geography entity. Now you can select 1 or multiple geographies for the user.

To report using this new security via SQL, simply write some SQL like this:

DECLARE @UserName NVARCHAR(100) = ‘DOMAIN\login’;

SELECT g.geoid,
FROM   myorg_mscrm.dbo.filteredsystemuser u
— join will be different depending on what type
       of relationship you created

       INNER JOIN myorg_mscrm.dbo.filterednew_geography g
ON u.new_geographyid = g.geographyid
INNER JOIN dbo.legacysystemdata ld
ON ld.geoid = g.new_externalid
WHERE  u.domainname = @UserName; 

This is the basic concept, but it can obviously be adapted to your organizations specific needs, as well as converted to FetchXML via an integration tool.

Show Default Geography
Similar to the previous scenario, start by creating a new entity with your geography, remembering to include an external id column (one that is a natural key of the entity), and perform an integration between the source system and Dynamics CRM.

Then, customize the user entity and add a new Lookup/1:M relationship to your geo entity, making sure to have the display name of the relationship something insightful, like ‘Default Geography’.

Here is some SQL that would be a typical example of a default geography for a parameter dropdown. This too, can be easily converted to FetchXml for Dynamics Online customers:

DECLARE @UserName NVARCHAR(100) = ‘DOMAIN\login’;

SELECT u.domainname,
g.new_externalgeoid AS DefaultGeoId,
g.geoname           AS DefaultGeo
FROM   myorg_mscrm.dbo.filteredsystemuser u
— will be different depending on
what relationship you created

INNER JOIN myorg_mscrm.dbo.filterednew_geography g
ON u.new_defaultgeoid = g.geographyid
WHERE  u.domainname = @UserName;

These are two very typical scenarios that enterprises often encounter when trying to secure and manage their data. Using these free Dynamics CRM capabilities to address these issues gives you the benefit of built in management of your users and doesn’t require adding any additional UI or custom tables.

Interested in learning more about how to use Dynamics CRM to secure your data? Leave your questions and comments below, or email us at 

Phone: 312-602-4000
222 W. Adams
Chicago, IL 60606
Show Buttons
Share On Facebook
Share On Twitter
Share on LinkedIn
Hide Buttons