CRM 2011: Special permissions missing for Users

Question

We have created a bunch of users in CRM 2011 using the SDK. However, we added their Security Role records through the database.

Everything seems to work fine, until these users started to save their own User Dashboards and Advanced Finds.

The users could create their own User Dashboards. However, once they created them, they could not see them. They were not in their list of Dashboards - only the System Dashboards where there.

There were no errors in the event viewer or even the trace logs.

I used SQL Profiler to see what it was doing and I discovered it was checking the PrincipalEntityMap table for principals that had an objecttypecode of 1031 - which is the User Dashboard (called UserForm).

How do these records get created?

I can write a SQL script to populate the database with these missing records.

What I would like to know is why they are missing? Any ideas?

Where do the records for PrincipalEntityMap come from?

Answer 1

Because we created the UserRole (i.e. User Security Role) records through the database and not through the SDK - we missed some POA (Principal Object Access) related records. There are a number of stored procedures that can be called to re-initialise these records. We have written a script to reset these records for all users:

-- This will create PrincipalEntityMap for users - if they are not there:
INSERT INTO PrincipalEntityMap (ObjectTypeCode, PrincipalId, PrincipalEntityMapId)
SELECT 1031, sup.PrincipalId, NEWID()
FROM SystemUserPrincipals sup 
INNER JOIN SystemUser su ON su.SystemUserId = sup.SystemUserId
WHERE 
(sup.PrincipalId = su.SystemUserId) AND
(sup.PrincipalId NOT IN
    (
        SELECT pem.PrincipalId 
        FROM PrincipalEntityMap pem
        WHERE pem.ObjectTypeCode = 1031
    )
)

DECLARE @PrincipalTable TABLE (PrincipalID uniqueidentifier)
DECLARE @CurrentPrincipalID uniqueidentifier
DECLARE @UserIds VARCHAR(60)
DECLARE @Type INT

BEGIN TRANSACTION ResetPrincipalEntitiyMap
BEGIN
    SET @Type = 8

    INSERT INTO @PrincipalTable (PrincipalID)
    SELECT sup.PrincipalId 
    FROM SystemUserPrincipals sup WITH (NOLOCK) 
    INNER JOIN SystemUser su WITH (NOLOCK) ON sup.SystemUserId = su.SystemUserId AND sup.PrincipalId = su.SystemUserId


    WHILE EXISTS (SELECT PrincipalID FROM @PrincipalTable)
    BEGIN

        SELECT TOP 1 @CurrentPrincipalID = PrincipalID
        FROM @PrincipalTable
        ORDER BY PrincipalID ASC

        EXEC p_PrincipalEntityMapReinit @CurrentPrincipalID, @Type

        EXEC p_PrincipalAttributeAccessMapReinit @CurrentPrincipalID, @Type

        SET @UserIds = cast(@CurrentPrincipalID AS VARCHAR(50))

        EXEC p_SystemUserBuEntityMapReinit @UserIds

        DELETE FROM @PrincipalTable WHERE PrincipalID = @CurrentPrincipalID

    END
END


COMMIT TRANSACTION ResetPrincipalEntitiyMap

Please Note: Always perform inserts/updates/deletes of Security Related entities (User, UserRole, Team, TeamRole, etc.) through the SDK - rather than the database. The SDK does some weird stuff in the background that will be missed if you use SQL.

Answer 2

While trying to resolve the common/constant problem with exchange server side sync on CRM 2013 (error code E-Mail-Server: Crm.80044151 when sync of contacts, tasks and appoitments is enabled), we've also tried to reinit the principal-tables using your script.
For CRM2013/15, it had to be modified slightly, because the signature of SP p_PrincipalEntityMapReinit has changed.
Here's the updated TSQL - maybe it helps someone else (in our case, it didn't :( ):

DECLARE @PrincipalTable dbo.EntityIdCollection
DECLARE @CurrentPrincipalID uniqueidentifier
DECLARE @UserIds VARCHAR(60)
DECLARE @Type INT

BEGIN TRANSACTION ResetPrincipalEntitiyMap
BEGIN
    SET @Type = 8

    INSERT INTO @PrincipalTable (id)
    SELECT sup.PrincipalId 
    FROM SystemUserPrincipals sup WITH (NOLOCK) 
    INNER JOIN SystemUser su WITH (NOLOCK) ON sup.SystemUserId = su.SystemUserId AND sup.PrincipalId = su.SystemUserId

    EXEC p_PrincipalEntityMapReinit @PrincipalTable, @Type

    WHILE EXISTS (SELECT id FROM @PrincipalTable)
    BEGIN

        SELECT TOP 1 @CurrentPrincipalID = id
        FROM @PrincipalTable
        ORDER BY id ASC

        EXEC p_PrincipalAttributeAccessMapReinit @CurrentPrincipalID, @Type, 1

        SET @UserIds = cast(@CurrentPrincipalID AS VARCHAR(50))

        EXEC p_SystemUserBuEntityMapReinit @UserIds

        DELETE FROM @PrincipalTable WHERE id = @CurrentPrincipalID

    END
END

COMMIT TRANSACTION ResetPrincipalEntitiyMap