Figuring out shares in the PrincipalObjectAccess POA table in CRM

Not a long article, this one is to support my recent presentation at the UK CRM User Group about security design and performance. I mentioned that when you are trying to figure out  what is going on in the PrincipalObjectAccess (aka “POA”) table, it can be useful to separate out things like Users and Teams, and furthermore to separate Owner Teams from Access Teams.

I also find it helpful to join to the right tables to get friendly names rather than go blind on GUIDs. Note: in the example code below I have pulled in first names for users to keep to shorter columns for demo purposes, in larger Orgs you might need full names, but it depends on what you are trying to troubleshoot.

I am excluding some object types from the results, because every user has their own User record and User Settings record shared with them. These are inevitable and not useful to most troubleshooting scenarios.

Of course, for some scenarios you will be trying to figure out why some users have access to records of a specific entity, in which case you might want to replace “POA.[ObjectTypeCode] NOT IN (8,150)” with an IN or an equals.

SELECT TOP 1000
CASE WHEN POA.[PrincipalTypeCode] = 8 THEN ‘User’
WHEN POA.[PrincipalTypeCode] = 9 AND TEAM.[TeamType] = 0 THEN ‘Owner Team’
WHEN POA.[PrincipalTypeCode] = 9 AND TEAM.[TeamType] = 1 THEN ‘Access Team’
ELSE ‘Other’ END AS ‘PrincipalType’,

COALESCE(USERID.[FirstName],TEAM.[Name]) AS PrincipalName,
POA.[ObjectTypeCode],ENTITY.[OriginalLocalizedName], POA.[ObjectId],
POA.[AccessRightsMask],POA.[InheritedAccessRightsMask],POA.[ChangedOn],
POA.[PrincipalTypeCode],POA.[PrincipalId]
FROM [MyOrg_MSCRM].[dbo].[PrincipalObjectAccess] AS POA

LEFT OUTER JOIN [MyOrg_MSCRM].[dbo].[SystemUserBase] AS USERID
ON POA.[PrincipalId] = USERID.[SystemUserId]
LEFT OUTER JOIN [MyOrg_MSCRM].[dbo].[TeamBase] AS TEAM
ON POA.[PrincipalId] = TEAM.[TeamId]
LEFT OUTER JOIN [MyOrg_MSCRM].[MetadataSchema].[Entity] AS ENTITY
ON POA.[ObjectTypeCode] = ENTITY.[ObjectTypeCode]

WHERE POA.[PrincipalTypeCode] IN (8,9) AND POA.[ObjectTypeCode] NOT IN (8,150)
/*User and Team (8,9) shares, not including shares to User or User Settings (OTC 8,150)*/

I am sure there are SQL gurus out there who could improve on this code. I also leave it as an exercise for the reader to add a join to the SystemUserPrincipal table to reverse engineer which users belong to which teams, so you can end up with a list of which users have access as themself or as a team member, in a single query.

Decoding the bit masks

In some cases, just being able to find rows in the POA that prove why a user has some kind of shared access to a record is enough. Knowing whether it is explicit or inherited, seeing if it is the user or an owner team or access team is usually enough to point you in the right direction to check or change your configuration.

If you do need to figure out exactly what rights have been shared, you need to decode the bit mask, and the table below is a quick reference to what the bits mean. You might also like to use Scott Sewell’s "POA Decoder Ring" spreadsheet if you don’t speak binary like a native: Unmasking CRM’s PrincipalObjectAccess table.

Bit value Meaning
1 Read
2 Write
4 Append
16 Append To (with shares, this always goes with Append, so treat them together as 20
32 Create. Of course this makes no sense – you can’t have permission to create a record that is already there, so you should not see this bit in use “in the wild”
65,536 Delete
262,144 Share
524,288 Assign
134,217,728 “This access is inherited” – added to the value of the share on the parent record and used for the child records when Share cascading behaviour is turned on.

One value you will see quite a lot in the InheritedRightsMask column is 135,069,719. This is basically all of the bits in the table above added together, except 32 (Create). You will see this for child records when Reparent cascading behaviour is turned on, with the owner of the parent record as the principal. It effectively allows the owner of the parent record to do anything they like to the child record, albeit always filtered by their security privileges (so if they do not have the rights to delete any records of this entity, they do not get to do that to this record either).

This same number will also show up if someone explicitly shares a record and ticks every box, then this share is cascaded down to child records. In real-world use I find that does not show up very often. Users rarely share records and give people access to do anything, it is usually more specific, eg to grant write and assign rights. Note: if a user does not have a privilege for a record, they cannot share that privilege to someone else, the check box would be disabled in the Share dialogue. So if Alice opens a record that she can read, but not delete, and shares that record with Bob, Alice will not be able to select the checkbox to share delete rights with Bob (which is good because it means it is obvious to the user that they cannot do this).

Countdown to CRMUG UK Meeting in Reading on 16th May 2013

It’s now just 10 weeks to go until the next UK CRM User Group meeting. We have another great agenda full of shared experiences and tips from your fellow Dynamics CRM users across a range of different sectors.

Thursday 16th May 2013, Microsoft TVP, Reading

Join us for the next UK CRM User Group Meeting at the Microsoft headquarters in Reading. Coffee and Reception start at 9:30 am. Don’t miss this opportunity to experience what the CRMUG has to offer you and other Dynamics CRM users!
Registration is now open! Click here to register for this event for FREE

Agenda

    • Registration & Coffee
    • Welcome & Introduction
    • Microsoft Dynamics CRM Roadmap & Demonstration
    • Member Showcase: Capita
    • Coffee Break
    • Best Practice Session:  Reporting/Workflow
    • Member Showcase:  NCFE
    • Networking Lunch
    • Discussion Topic:  Dynamics CRM – 20 Top Tips from an MCT
    • Q & A Session
    • Close at approximately 4:00 pm
    Who should attend?

All Dynamics CRM users are welcome. We also encourage you to invite your colleagues and other users – email to colleagues, tweet about it, mention it in your LinkedIn groups and other sites..

Partners are welcome to attend provided they are accompanied by a customer. Partners please bear in mind that the objective of regional events such as this is to exchange knowledge and Dynamics CRM experience. Partner personnel should possess deep CRM expertise and refrain from sales activities.

Hope to see you there!

Social Evening

We are talking about getting together for an informal social and networking event on the evening of 15th May before the main event . This would be at a venue in central Reading within walking distance from railway station, TVP shuttle routes and plenty of hotels. Plans are coming together so we should be able to announce more details soon. Whatever we do it will definitely be free to come and join in, and we’ll be trying to get a deal wherever we go to try and keep your food and drinks bills as low as possible.

I’ll post an update once we get a plan together. <edit – see this post about the CRM User Group Social Evening May 15th in Reading />

Help get better and more helpful tooltips on forms in CRM 2011

One of my frustrations in CRM has long been that there is no easy way to get useful tooltips to appear to help end users when they are filling in forms.

Loads of discussions take place during planning meeting about what fields will be included, what their purpose is, what should or should not go into a particular field and what will happen when a user fills it in (eg scripts or workflows, or how the data surfaces in a report). Most of that detailed consideration gets buried in project documents, some of it may be covered in user training, and even less may actually make it into end-user support articles and training hand-outs.

Some of these are common misunderstandings, such as what should you use “Address Name” for? (hint – it’s not the building name nor part of the address, it’s the name of the address, i.e. what do you call this place? answer might be “Head Office” or “North West Distribution centre” for example).

The most obvious place to put this stuff is on the form, and the most logical place is right next to where it is needed, by adding tooltips to the field labels. Yes, I know they do have tooltips, but these simply repeat the text of the field label! Zero value is added by having them there, they don’t even go as far as using the “normal” display name or the description.

There are ways to create tooltips by adding an “onmouseover” event to the field, but since this is a direct DOM hack, this won’t be supported. It is also yet another bunch of scripts being delivered over the wire, which also need to be written, maintained and updated, usually by system admins with at least a basic level of Jscript coding skills.

There must be a better way…

For me the ideal answer would be a property of a field on a form where a system customiser (i.e. not admin, no coding skills required) can easily add text they want to use as a tooltip. Either directly as custom text or perhaps simply ticking a couple of boxes to include the field display name and/or description and/or schema name and/or custom text as well.

So I went over to the Microsoft Connect site where you can give feedback about CRM and other products. This is intended for product suggestions and definite bugs, rather than support for things that don’t work in your particular implementation. Other site users can vote up or down the importance of your suggestion, and can comment or provide information about workarounds.

I posted a new suggestion to provide useful “tooltips” on rollover/mouseover of form labels and/or allow supported customisation of them and within two weeks it had already reached the all-time top ten most upvotes. It is continuing to rise and I really hope this is given serious consideration for the next version (either a quarterly release, or more likely the next “full version” given that it might require changes to xml schema for exporting and importing customisations).

You can help get this idea implemented

If you like the idea go and vote it up and if have useful input add a comment so that if and when it gets implemented they can make it fit people’s real-world needs as closely as possible.

There are loads more great suggestions in the Dynamics CRM section – have a look round and vote for those you would most like to see built first.

CRM 2011 Opportunity Revenue field is read only

I’ve just had a slightly strange situation with some customisation for a CRM online project I am working on that I thought I would share in case anyone else has a similar experience with this particular scenario or other odd results of customisations which may have a related cause.

Customising the Opportunity form

I have been customising various entities and forms to build the system to suit the particular customer’s requirements. One of the things I was changing was the Opportunity form. I added some of the custom fields, moved some things around and tidied is up generally. Published and everything seemed fine.

Estimated Revenue always read only

Then I noticed that I could not put a value in the Estimated Revenue field. It was disabled, dimmed as unavailable, read-only, “move along, nothing to do here…”. Nothing I did would change this, Est. Revenue was always read only.

I had quite deliberately already changed the “IsRevenueSystemCalculated” field default to “User Provided”, and this is the value it correctly showed up on the form.

In general this organisation will be quoting their clients as part of longhand written proposals or formal RFPs for very flexible services work which does not lend itself well to using the Product Catalogue, although they may do that later for standard, fixed price, “commodity” services they offer. So their Opportunities will be used to manage the sales pipeline but not to figure out the values for them, and user provided figures are the most sensible way to handle this.

If I changed isrevenuesystemcalculated to “System Calculated” it correctly added in a value (£0.00 at the moment since I have added no line items) and it remained disabled, as it should. Change it back to “User Provided” and nothing happens, still read-only and unavailable. Currency was set, no Price List was added (and none needed as there would be no line items). All very strange.

What else could be causing this?

I had included Est. Revenue in the form header, and thought this might be causing the problem in some way because it would be a read-only field, but I removed it and it made no difference. I checked and rechecked that there were no scripts or anything else that could be affecting this behaviour. Nothing.

But one other thing that I had changed from the default OOBE is the way the field was displayed – rather than a pair of radio buttons I had chosen to save some space on the form by showing isrevenuesystemcalculated as a picklist since the user would only very rarely want to change this.

Switching back to radio buttons fixed the problem.

Why would this be buggy?

So it seems that the built-in functionality which is triggered by changing this field and updating the Est Revenue field accordingly is not particularly flexible. As far as my testing shows, it looks like it explicitly uses the status of the radio buttons as part of the DOM, rather than the underlying value of the bit field to figure out the state of the user selection in the isrevenuesystemcalculated field.

I would argue that this is a bug, since it should be possible to display this field in any way I choose. Albeit if I chose a single check box the label would need to be more explicit than simply “Revenue”, and this would not work as tidily in any case as selecting or clearing a checkbox does not trigger an “onChange” event until the focus changes (ie you click away from the field).

Have you had any similar experiences where the built-in functionality is very picky about how things are displayed, or where changing the default forms has affected things in strange ways? Please feel free to share via the comments.

Don’t let CRM overload users’ email inboxes

EmailSpam

Matt Keenan wrote an interesting post about some of the CRM deadly sins over at his Dynamics Café blog. One of his categories that I particularly see happening all too often is “too much notification”.

Why do people do this?

I see this in initial customer specifications and requirements documents quite a lot, usually in the form of “When foo happens, send an email to person/group X”.

This is often born out of naïvety on the part of the person who has been tasked with figuring out how their processes should be managed using a system they have never seen yet. They respond by falling back on what they know and try to use their existing mechanisms (such as email) to tell people when something important happens.

I also find this in existing systems, sometimes where the original consultant has simply felt that if that is what the customer asked for, that is what they should be given. In other cases it is because not enough time has been spent on looking at alternatives or on training end users how to find information for themselves without having it pushed out to them over old channels.

I tend to be a bit more argumentative and ask “why do you want it do that?” “have you considered other options such as…”. Of course, if someone insists loudly enough I deliver what the customer has asked for, but I do at least try to explore with them whether they are taking the best approach.
Find out how to replace notifications, and when they are the right option»

Annoying file format warning when exporting CRM records to Excel

Pointless error message dialog box

When you export from CRM to Excel the data is derived as XML, saved with an XLS file extension and Excel is invoked to open the temporary file. Unfortunately Excel checks to see if the file being opened is actually of a type which matches the file extension and tries to be helpful. Normally this is to help overcome problems such as a comma-separated variable (CSV) file being saved as an XLS file extension, which ought to mean Excel tries to read the XLS file, fails because the contents are nothing like a real Excel binary file and gives up. Instead, Excel actually looks at the content, spots that it looks very much like a CSV and allows you to open it just as if the file extension was correct in the first place. However, this cleverness is tempered somewhat by the fact that the default setting for this is to ask the user every single time what they want to do.

As always, this is probably intended to be a helpful warning and prevent people opening files which might have insecure content, but it fails to do so because most users do not understand the implications and the longwinded message is probably not even read properly anyway. Certainly the 50th time someone sees a dialog like the one below, they just click “yes” without reading and it no longer provides any benefit whatsoever (by the way, I have done nothing to this, it displays in this ridiculously wide, un-resizable window on my machine).

Click to see larger version - CRM Excel export error message

Whenever I have managed people in IT support roles I try to eliminate fixes which involve things like “ignore that error message, just hit OK and it will work fine”. This not only numbs people to the meaning of that particular error message but to these sorts of warnings in general. Too often I have heard users explain why they did not report a problem until it was too late, saying “well, I got an error every day saying something about faulty disk or something but I just clicked OK, like John said we should with that other one…”. Find the root cause, eliminate the error, or suppress the error somehow, don’t teach people that errors don’t matter or they just ignore them. If you went to your doctor and said “it hurts my neck when I lift my arm up” you would not be impressed if she replied “then don’t lift your arms up!”, would you?
Read on to find out how to stop Excel asking unhelpful questions when you export records from CRM»

Outlook client for CRM 4 with rollup 10

MS Dynamics logo

Finally Microsoft have released a client installation package with a recent rollup already included in the package (“slipstreamed”). Unfortunately, nearly 4 weeks after the release of update rollup 11 it is only rollup 10 that is included. While this is a move forward from the rollup 7 client that was available, it still means that most people are going to need to install the client and then immediately apply a patch to UR11, so it is probably of limited help really. Thanks but no thanks.

Get the new CRM 4.0 client for Outlook with Update Rollup 10 if you are using the on-premise or partner-hosted (“service provider”) versions of CRM. If you are using CRM online you are stuck with installing the original version of the special online client and patching it yourself. Note: CRM on-demand from Microsoft is only available in the US and Canada despite the announcement back in April about worldwide release, because it seems that will only be for the next release, version 5 available later this year.

Hat tip to The CRM Business for the original heads-up on this one.