Working with CrmSettings via PS – setting things you didn’t know you could, or should

Working with CrmSettings via PS – setting things you didn’t know you could, or should

Recently I have been trying to fix some Server Side Sync issues with an on-prem Dyn CRM 2016 and this got me to dig into the nitty gritty of settings for the system.

Before going berserk and chaning a lot of these settings, be aware that you can cause severe problems with your system if you are not careful so I would strongly recommend that you test any settings and also export any settings so that you know what they are.

Finetuning your onprem installation is something that you can do with an onprem that is a lot harder with online.

By logging in on your CRM-server, and opening Powershell and entering the following command, you can start working with some settings that are otherwise hard or impossible to reach:

Add-PSSnapin Microsoft.Crm.PowerShell

For instance, if you want to check out some of the ServerSideSync Settings, enter:

Get-CrmSetting -SettingType ServerSideSyncQueueSettings

If you are using the Windows Power Shell ISE, after loading the Microsoft.Crm.PowerShell, you will get intellisense that will help you see which different settings are available. So if you write:

Get-CrmSetting -SettingType

And then enter a space after this, you will see the different options:

For some more details into Server side sync and how to configure this in detail, look here:

And here is an example of how to set data.

$set = Get-CrmSetting -SettingType ServerSideSyncQueueSettings 

$set.MailboxQueueItemsInMemoryHigh = “500” 
$set.MailboxQueueItemsInMemoryLow = “200” 

Set-CrmSetting -Setting $set

Just add more $set – statements to set more values.

If you want to write some settings to a file, just pipe the output to a file, for example:

Get-CrmSetting -SettingType ServerSideSyncQueueSettings > ServerSideSyncSettingsOrg.txt

But as I mentioned above, be careful with these settings.

Gustaf Westerlund
MVP, Founder and Principal Consultant at CRM-konsulterna AB

Be aware of Synchronous Workflows on incoming emails and Queue items

Be aware of Synchronous Workflows on incoming emails and Queue items

How synchronous workflows that fail, make emails disappear.

During the last few Days I have been having the not so pleasant experience of trying to find some really nasty errors in incoming emails to a queue in CRM. We are using CRM 2013 SP1 (on prem) and hence are using the Server Side Sync. Everything had been working great and we had developed some neat workflows that did some magic to convert emails to cases (as those features were not available, and not good enough either) when we develop our stuff.

Well, thing stopped working, I tried sending emails to the CRM server, but none were received in CRM. All I got in CRM was a not so descriptive general alert saying:

“An error occurred while creating incoming email in Microsoft Dynamics CRM for the mailbox XYZ. To view the email that failed, see the alerts in the mailbox record.”

and in the mailbox:
“An error occurred while creating the incoming email “Test 1512″ in Microsoft Dynamics CRM for the mailbox XYZ.”

As I am working on my telepathic abilities, I have not yet reached a level where I can understand what is wrong so I had a look in the trace, and I was very happy I was running an on-prem server. It is a bit long, don’t worry if you don’t understand it all, nobody (I think) does.

[2015-05-15 15:21:13.459] Process:CrmAsyncService |Organization:673c5996-a1fa-e311-93ed-00155d0a831d |Thread:    4 |Category: Platform.Metadata |User: 00000000-0000-0000-0000-000000000000 |Level: Error |ReqId:  | <>c__DisplayClass1.<LoadMetadataContainerFromDatabase>b__0  ilOffset = 0x14
>Multi-org sharable cache loading system and non-system metadata with build number and language 1033
[2015-05-15 15:33:45.132] Process:CrmAsyncService |Organization:00000000-0000-0000-0000-000000000000 |Thread:   26 |Category: Exception |User: 00000000-0000-0000-0000-000000000000 |Level: Error |ReqId: cf367175-12a2-4719-a5af-f53f7f254399 | CrmException..ctor  ilOffset = 0x7
 at CrmException..ctor(String message, Exception innerException, Int32 errorCode, Boolean isFlowControlException)  ilOffset = 0x7
 at CrmException..ctor(String message, Exception innerException, Int32 errorCode)  ilOffset = 0x5
 at Exceptions.ThrowIfGuidEmpty(Guid parameter, String name)  ilOffset = 0x18
 at OrganizationSdkServiceInternal.Retrieve(String entityName, Guid id, ColumnSet columnSet, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode)  ilOffset = 0x16
 at InprocessServiceProxy.RetrieveCore(String entityName, Guid id, ColumnSet columnSet)  ilOffset = 0x28
 at OrganizationServiceProxy.Retrieve(String entityName, Guid id, ColumnSet columnSet)  ilOffset = 0x4
 at EntityDictionary.TryRetrieveEntity(String key)  ilOffset = 0xF8
 at EntityDictionary.System.Collections.Generic.IDictionary<System.String,Microsoft.Xrm.Sdk.Entity>.get_Item(String key)  ilOffset = 0x1B
 at VisualBasicValue`1.Execute(CodeActivityContext context)  ilOffset = 0x3A
 at ActivityExecutor.ExecuteInResolutionContext(ActivityInstance parentInstance, Activity`1 expressionActivity)  ilOffset = 0x35
 at InArgument`1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor)  ilOffset = 0x2F
 at ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary`2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate)  ilOffset = 0x16
 at ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary`2 argumentValueOverrides, Location resultLocation, Int32 startIndex)  ilOffset = 0x9C
 at ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)  ilOffset = 0x1C
 at ActivityExecutor.OnExecuteWorkItem(WorkItem workItem)  ilOffset = 0x53
 at Callbacks.ExecuteWorkItem(WorkItem workItem)  ilOffset = 0xD
 at Scheduler.OnScheduledWork(Object state)  ilOffset = 0xC5
 at SendOrPostThunk.UnhandledExceptionFrame(Object state)  ilOffset = 0x0
 at PumpBasedSynchronizationContext.DoPump()  ilOffset = 0x2E
 at WorkflowApplication.Invoke(Activity activity, IDictionary`2 inputs, WorkflowInstanceExtensionManager extensions, TimeSpan timeout)  ilOffset = 0x23
 at WorkflowInvoker.Invoke(Activity workflow, IDictionary`2 inputs, TimeSpan timeout, WorkflowInstanceExtensionManager extensions)  ilOffset = 0x36
 at SynchronousWorkflowActivityHost.ExecuteWorkflowUsingInvoker(Activity workflow, ICommonWorkflowContext context)  ilOffset = 0xD0
 at SynchronousWorkflowActivityHost.StartWorkflow(WorkflowActivationData activationData, ICommonWorkflowContext context)  ilOffset = 0x73
 at SynchronousWorkflowActivityHost.StartWorkflow(ICommonWorkflowContext context)  ilOffset = 0xBA
 at WorkflowProcessServiceInternalHandler`1.ExecuteSyncWorkflow(Guid workflowId, PipelineExecutionContext pipelineContext, IGenericEventData workflowInstanceData, Boolean isTriggered)  ilOffset = 0x59
 at WorkflowProcessServiceInternalHandler`1.ExecuteTriggeredSyncWorkflow(Guid workflowId, PipelineExecutionContext pipelineContext)  ilOffset = 0x21
 at SyncWorkflowExecutionPlugin.Execute(IServiceProvider provider)  ilOffset = 0x2C
 at V5PluginProxyStep.ExecuteInternal(PipelineExecutionContext context)  ilOffset = 0x2A3
 at VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)  ilOffset = 0x65
 at Pipeline.Execute(PipelineExecutionContext context)  ilOffset = 0x65
 at MessageProcessor.Execute(PipelineExecutionContext context)  ilOffset = 0x1FB
 at InternalMessageDispatcher.Execute(PipelineExecutionContext context)  ilOffset = 0xE4
 at ExtensiblePlatformMessageDispatcher.Execute(PipelineExecutionContext pluginContext)  ilOffset = 0x0
 at ExtensiblePlatformMessageDispatcher.CreateWithInvocationSource(BusinessEntity entity, Int32 invocationSource, ExecutionContext context)  ilOffset = 0xB4
 at BusinessProcessObject.Create(IBusinessEntity entity, ExecutionContext context)  ilOffset = 0x54
 at GenericActivityServiceBase.Create(IBusinessEntity entity, ExecutionContext context)  ilOffset = 0xA1
 at CommunicationActivityServiceBase.Create(IBusinessEntity entity, ExecutionContext context)  ilOffset = 0x1A
 at EmailService.SetRecipientsAddAdditionalAttributeAndCreate(AddressEntry[][] allResolvedAddressEntries, Email email, Entity emailDeltaEntity, String traceSubject, ExecutionContext context)  ilOffset = 0x1DA
 at EmailService.Deliver(Boolean userPromote, Guid emailId, String messageId, String subject, String from, String to, String cc, String bcc, DateTime receivedOn, String submittedBy, String importance, String body, BusinessEntityCollection attachments, Guid campaignResponseId, Entity emailDeltaEntity, ExecutionContext context, Boolean validateBeforeDeliver)  ilOffset = 0x524
 at EmailService.DeliverIncoming(String messageId, String subject, String from, String to, String cc, String bcc, DateTime receivedOn, String submittedBy, String importance, String body, BusinessEntityCollection attachments, Entity extraProperties, Boolean validateBeforeCreate, ExecutionContext context)  ilOffset = 0xB8
 at RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)  ilOffset = 0xFFFFFFFF
 at RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)  ilOffset = 0x25
 at RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)  ilOffset = 0xCF
 at LogicalMethodInfo.Invoke(Object target, Object[] values)  ilOffset = 0x4F
 at InternalOperationPlugin.Execute(IServiceProvider serviceProvider)  ilOffset = 0x57
 at V5PluginProxyStep.ExecuteInternal(PipelineExecutionContext context)  ilOffset = 0x200
 at VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)  ilOffset = 0x65
 at Pipeline.Execute(PipelineExecutionContext context)  ilOffset = 0x65
 at MessageProcessor.Execute(PipelineExecutionContext context)  ilOffset = 0x1C5
 at InternalMessageDispatcher.Execute(PipelineExecutionContext context)  ilOffset = 0xE4
 at ExternalMessageDispatcher.ExecuteInternal(IInProcessOrganizationServiceFactory serviceFactory, IPlatformMessageDispatcherFactory dispatcherFactory, String messageName, String requestName, Int32 primaryObjectTypeCode, Int32 secondaryObjectTypeCode, ParameterCollection fields, CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId, Guid transactionContextId, Int32 invocationSource, Nullable`1 requestId, Version endpointVersion)  ilOffset = 0x16E
 at OrganizationSdkServiceInternal.ExecuteRequest(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, UserAuth userAuth, Guid targetUserId, OrganizationContext context, Boolean returnResponse, Boolean checkAdminMode)  ilOffset = 0x1F1
 at OrganizationSdkServiceInternal.ExecuteRequest(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode)  ilOffset = 0x2D
 at OrganizationSdkServiceInternal.Execute(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode)  ilOffset = 0x26
 at InprocessServiceProxy.ExecuteCore(OrganizationRequest request)  ilOffset = 0x34
 at IncomingEmailProviderBase.DeliverMessageInternal(EmailMessage emailMessage, Boolean validateBeforeCreate)  ilOffset = 0x1B7
 at IncomingEmailProviderBase.DeliverMessage(EmailMessage emailMessage, Boolean validateBeforeCreate)  ilOffset = 0x19
 at GetItemsStep.ProcessResponse()  ilOffset = 0x5AB
 at ExchangeIncomingEmailProviderStep.EndOperation()  ilOffset = 0xFC
 at ExchangeIncomingEmailProvider.ReceiveEmails()  ilOffset = 0x92
 at IncomingActivityProviderBase.Run()  ilOffset = 0x42
 at MailboxEmailOperation.PerformOperation()  ilOffset = 0x2C
 at MailboxOperationBase`1.Execute()  ilOffset = 0xAA
 at MailboxProcessingOperation.PerformOperation()  ilOffset = 0x1A
 at MailboxOperationBase`1.Execute()  ilOffset = 0xAA
 at MailboxOperationCommand.InternalExecute(MailboxAsyncEvent asyncEvent)  ilOffset = 0x64
 at AsyncCallbackHandler`2.ResumeExecution(IAsyncEvent asyncEvent)  ilOffset = 0x56
 at AsyncEventExecutionManager`2.ExecuteHandler(IAsyncEventHandlerFactory handlerFactory)  ilOffset = 0x8A
 at PoolHandler.ProcessAsyncEvent(IAsyncEventExecutionManager asyncEventExecutionManager)  ilOffset = 0x144
 at AsyncEventOperation.<.ctor>b__2(IServiceOperation operation)  ilOffset = 0x0
 at FaultToleranceBehavior.Execute(ServiceOperationAction operation, IServiceOperation operationParameter)  ilOffset = 0x18
 at MonitoredOperation.Execute()  ilOffset = 0xD
 at ThreadPoolQueueExecutionEngine.InvokeNextOperationInThreadPool(Object state)  ilOffset = 0xC
 at ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)  ilOffset = 0x70
 at ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)  ilOffset = 0x4
 at QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()  ilOffset = 0x30
 at ThreadPoolWorkQueue.Dispatch()  ilOffset = 0xA3
>Crm Exception: Message: Expected non-empty Guid., ErrorCode: -2147220989, InnerException: System.ArgumentException: Expected non-empty Guid.
Parameter name: id
[2015-05-15 15:33:45.304] Process:CrmAsyncService |Organization:00000000-0000-0000-0000-000000000000 |Thread:   26 |Category: Platform |User: 00000000-0000-0000-0000-000000000000 |Level: Error |ReqId: cf367175-12a2-4719-a5af-f53f7f254399 | ExceptionConverter.ConvertToFault  ilOffset = 0x69
>UNEXPECTED: no fault?

My first thought was that the stack trace was really long… and then there was some mentioning of User with Guid 00000000-0000-0000-0000-000000000000.

Another kind of queue ticket

As the Server Side Sync has something of a rep for not being the most reliable guy in Town, I wasn’t really sure if this error really was an error that we had caused or if there was something wrong with the server side sync. As my perception was that we hadn’t really changed any thing, I started with the recreating the mailbox, recreated the server profile, ran test and enable and that worked, but when I tried sending my own emails, from different email accounts, as you never know if there is something spooking it with a specific email address, I still never got the queue to read any emails, not even the test emails created by CRM. I was starting to get a bit short on hair by this time, so I resorted to my classical troubleshooting method of backing off as far as possible and seeing if things still were broken, and I hence deactivated all workflows and plugins that were associated to email and Queue item, and gave the user full access and I was once again awarded with this method, emails started arriving in the queue in CRM.

I then revoked admin access and started re-enabling each single workflow/plugin until thing started
breaking again. This turned out to be a painstaking process as the only way I found to test this was to send an email to CRM and then wait for about 5 minutes until it was/or was not pulled into the queue.
It took me more or less a days work just to find the two workflows that were causing the problems.

The actual problems in them selves, arn’t really that interesting, it turned out that the error message above was actually quite correct, If you try to set a null-user to an owner field, CRM will respond like this.

What I did notice however was that the workflows that we were using on “queue item” in this case were synchronous (yes, yes, we do have good reasons for it, I hope you do to, Otherwise, please choose asynchronous/background workflows) and as they broke, CRM did two interesting facts that are not very desirable and noteworthy:

  • The mailbox was shut down for further email processing. It had to be “Test and Enabled” to start receiving emails again. I have yet to see a good way to do this in an automated fashion and to be able to monitor the mailboxes to see if they are working as expected.
  • The email that was the cause of the breaking of the workflow, got lost. Even after the mailbox got reactivated, it had marked this email as read and did not read it into CRM despite the fact that it was neither in the queue nor as a lone activity which might have been the case if the Creation of the queue item broke. But obviously the entire process is handled atomically.

Based on this I have the following recommendations:

  1. If possible try to avoid an synchronous logic on receiving email or Creation of queue items. Try to move logic to asynchronous as this will cause errors to be non-fatal, like the two types of effects that are hard to handle above.
  2. If you do need to have synchronous logic on the receiving of emails or Creation of queue items try to add conditions in the beginning that will ensure that the logic only runs in those very specific cases where they are supposed to. Be aware of the fact that queue items can be associated to many different entities, so don’t rely on the fact that they should be only connected to something specific. At least take into consideration the fact that they will be connected to emails at some Point in time.
  3. I read an interesting fact today, that the activation of business rules Controls in which order they will be executed. If you are using CRM 2015 with server side (Entity) scope, this could probably cause similar issues if the activation is done haphazardly and causes the execution to be done in a way that can cause a bug and break processing of the queue item or email. I havn’t checked if these are run pre- or poststate but I think that depends on the action.
  4. If you get errors, especially when using CRM Online, try to replicate the error in the sandbox Environment and then either move backwards to a Point where it starts working again, or deactive Everything related to emails and queue items, make sure it works, and move forward. When you know where the error is, finding what the error is, is so much easier.

We also found a related bug in CRM today, if you try to create an asynchronous workflow that triggers on a field which has field based rights activated on it, it will break, with some weird error message about not being able to change domain for the user. The simple fix for that is to make the workflow synchronous and to make it run as the user that activates it, that worked. We didn’t try to run it as the user who created the workflow, as that is the same logic that is used in asynchronous workflows, I wasn’t too keen on trying something that was more likely to fail. If you have tried it, please leave a comment below.

I hope to see a lot of people tomorrow on the 19:th at the Swedish CRM UG Meeting!

Gustaf Westerlund
MVP, Founder and CTO at CRM-konsulterna AB

Collation and MVP Renewal

Collation and MVP Renewal

When working with CRM systems in non-English countries you often need to take certain aspects into consideration that might have very dire consequences if set incorrectly, one of these is the database collation.

The collation of the database is simplified how it orders characters. For those of you not used to working with multi-language installation with odd characters this might not be something you have considered. As I am Swedish let me give you an example. The Swedish alphabet looks as follows:


However using the collation that is default when using English as language in an on premise installation, which is “Latin1_General_CI_AI” (CI= Case Insensitive, AI = Accent Insensitive) the Swedish alphabet would be sorted as follows:


This is why when installing an organization that is to be used in Sweden, despite the fact that you might have chose to have English as base language, that you need to select the collation that sorts the Swedish characters correctly which for CRM is “Finnish_Swedish_CI_AS”.

On-premise setup of CRM – ability to select Finnish_Swedish_CI_AS collation
despite using English as base language

When setting up an organization in CRM online, you cannot chose the collation explicitly which is regrettable and I hope there will be some advanced setting in the future which will allow for this when provisioning new organizations. Check out what happened when I provisioned a new organization in English indicating that I was in Sweden.

The first pick list is for country – I selected “Sweden”, the last is for language and I changed from “Swedish” to “English”, which were the two only options.

After going through all the three steps you are shown a last instance configuration step.

In the instance configuration screen you can select the base language but there is no selector for collation. I selected “Engelska”, which if you have any imagination, means “English”.

After, a few seconds, yes, the new installation process is great and really fast, my org is up and running and I jump in to contacts and create to contact and check out the sorting. Any bets?

As you can see selecting English language will also implicitly select Latin1_General _CI_AI as collation which is incorrect from a Swedish perspective, I would have liked to see Östen Svensson at the end of the list, not sorted as “O”. 

Collations are integral to the configuration of the SQL database and there are many features of the database like ORDER BY and indexes that depend on the collation why changing collation of a database is very very tricky. It is actually so tricky that it is not supported if you manage to do it with an on-premise, which I have heard rumours of that some people have managed to do. On an online you cannot request it. You will be recommended to migrate the data from the old org to the new.

So let’s hope Microsoft will enhance the provisioning experience for CRM Online so that it includes a selector for collation. In the meantime, if you need a special setup, like the one I tried to set up above, English with Finnish_Swedish_CI_AS collation on CRM online I would suggest contacting Microsoft Online support to get their assistance in setting up the instance correctly. Hopefully they can assist in this.

And on another note, I was awarded the MVP award for the third year! A great honour and this very article is dedicated to the very award as two of my fellow MVP:s Shan McArthur and Niel Benson provided with background information to this article. I extend to you a humble thanks!

Gustaf Westerlund
MVP, CEO and owner at CRM-konsulterna AB

Customer databases and Wikileaks

The latest news concerning wikileaks have some very important implications on CRM systems or xRM systems in general for that matter. How do you set the system up to avoid large information losses? There are some general things to be taken into consideration and some specifics for Microsoft Dynamics CRM.

For many companies, the list of customers, cases, interesting leads and business opportunities are among the most critical information the company has. If it gets into the wrong hands, the effects can be anything from embarssing to fatal. The latest weeks news concerning wikileaks has put this risk into some new light and it is a good time for companies to really put the right focus on this and handle the problem before it is too late. A competent CRM system like Microsoft Dynamics CRM, can, if security issues have not be properly addressed, be a great tool to very quickly export a lot of very business critical information.

There are some general tips that you really should address:

– How critical is the data? Which data is the most critical? Try to focus on the most important data instead of trying to set up fine masked security to cover all data. This will give you a bigger bang for the buck and also get the changes up and running quickly. Remember the fact that the chains often breaks at the weakest link, so focus on this link first.

– What legal aspects of the data do you have? Do all employees sign non-disclosure agreements and do they understand the severity of actually taking along some data to a competitor. In reality it is very hard to drive legal actions based on this but making sure all employees have understood the severity, will act proactivly to reduce the risk.

– Who can access the data? Usually not only the employees, IT-consultants, CRM- and ERP-consultants, and other contracted people might also have access.Trying to reduce the number of contractors, and signing company global NDA:s with contractors is usually a good idea.
– Where is the data stored? In these cloud computing times, this is not always a simple question. Data might be stored in a country with very rigid anti-terrorist or anti-piracy laws allowing government or other agencies to demand access to the data. If these government agencies judge that it be in their contrys best interest to send this information forward, this might also be done. Might sound a bit paranoid, but security policy is more about being paranoid than being naive. I would recommed hosting the system yourself or at a local partner. Preferably a partner of similar size to your own company since this will give you the same amount of flexibility and beaurocracy. This local partner will also be under the same national laws as your own company and have a more intimate relationship with your business than a huge corporation with a global hosting service.
– What is the weak link in the handling of data? It does not really matter if the CRM system in the cloud has astronomical encryption in the database and data transfers, if the people using the system have the same password in the CRM system as they have in all other online services like Facebook or Hotmail. Numerous examples have shown that people do share passwords between sites, and that cracking one site usually unlocks a lot more. An example can be a person using the same password for their local childcare portal as they do for their CRM at the global company they work for. The simple childcare portal, might be easily hacked with normal methods like SQL-injection and the passwords generated from this can then be used to access the global company CRM.
There are still more general principles to follow, I will not list them all here, if you have any you find particulary important, please leave a comment!
So, how do we handle this in Microsoft Dynamics CRM? There are several techniques that can be used but it is a constant battle between giving your users the power to really work with the data and making sure that the data is safe. Bellow are some of the more common ways of handling this:
– Security Roles and business units. The basic security architeture of Microsoft Dynamics CRM is really versatile and has very good support for separating users and data into different business units and then setting user roles to restrict access based on these business units. For instance, a team of telesales personell with a very high turnover of employees, can be set to only have read and write access to their own customers and opportunities and their team manager has the task of delegating the ownership of leads or opportunities to them. By using different roles, the senior sales team can on the other hand have access to all customers and business opportunities in the system. If a good separation of data can be done based on business units and security roles, this is a very good method since it is easy to set up and change, and still has very deep functionality in Dynamics CRM, going all the way down to the Filtered Views in the Dynamics CRM SQL-database.

– Disabling Excel export. Probably the most risky function in Dynamics CRM in regards to data theft from employees, is the Excel button. It can export any data the user has access to. There is a flag in the security roles, where this function can be switched off. It should be for all but powerusers, analysts and management.

– Limiting Excel export size. There is a way of manipulating the Dynamics CRM database to only allow a certain amount of rows in an excel export. As I have understood it, it is really a way of easing the load on the server and not really meant as a means of protecting data. It can only be set on a system wide way, which will limit the use of Excel for all users. You can read more about it in this blog entry, have a look in the comments, since it tells you how to set this for CRM 4.

– Custom Plugin code. Writing code that uses more complex functionality and filters the data can also be used. It need to trigger on the Retrieve, RetrieveMultiple and Execute methods. This is of course the moste versatile method. Even though it can be used to filter data that is accessed from the Dynamics CRM GUI it does not affect the Filtered Views in the database, so it is not a 100% solution but will work in most cases.

– Unsupported customizations. There is of course the dark side of customizations as well, by rewriting the database with new stored procedures, views, by modifying the existing CRM functionality, very deep changes can be made. This is not something I recommend since it will usually require deep reverse engineering and will seriously affect the upgradablity of the Dynamics CRM system.

This is a complex area and I would be happy to discuss it with you. Please leave a comment with your views on the subject. All comments are moderated to avoid spam.

Gustaf Westerlund
CEO, Chief Architect and co-Founder at CRM-konsulterna AB