CRM PrincipalObjectAccess(POA)

PrincipalObjectAccess (POA) table is an important table which holds all grants share on CRM objects. This is just for understanding purpose.

 

SELECT TOP 100
 [PrincipalObjectAccessId]
 ,[PrincipalId]
 ,[PrincipalTypeCode]
 ,[ObjectId]
 ,[ObjectTypeCode]
 ,[AccessRightsMask]
 ,[InheritedAccessRightsMask]
FROM
 [PrincipalObjectAccess] WITH (NOLOCK)

 

PrincipalObjectAccessId – The GUID of share record.
PrincipalId – The GUID of the User or Team receiving the Share.
PrincipalTypeCode – indicates whether it’s a User or Team. -- 8 user 9 team
ObjectId – The GUID of the specific object being shared.
ObjectTypeCode – The object type of the record being shared.
AccessRightsMask – This field stores the rights granted directly via the ‘Sharing’ dialog.
InheritedAccessRightsMask – The rights applied by the system through cascading or reassignment processes are stored in this field.

check microsoft query to control this table data..

 

 

InheritedAccessRightsMask – The rights applied by the system through cascading or reassignment processes are stored in this field.

1 User

2 Business Unit

4 Parent: Child

8 Organization


Decoding the RightsMask Fields (AccessRightsMask and InheritedAccessRightsMask)

0               No permission
1                  Read
2                  Write
4                  Append
16                AppendTo
32                Create
65536           Delete
262144          Share
524288          Assign
134217728    Undocumented

for example 
852023 (1+2+4+16+32+65536+262144+524288) - ALL documneted permissions

Whenever a record is shared against a User or a Team, CRM will be tracking those entries in PincipalObjectAccess table and same will be queried using FilteredView.

FilteredAccount View part of code

or

[Account].[AccountId] in
(
select  POA.ObjectId from PrincipalObjectAccess POA
join SystemUserPrincipals sup (NOLOCK) on POA.PrincipalId = sup.PrincipalId
where sup.SystemUserId = u.SystemUserId and
POA.ObjectTypeCode = 1 and
((POA.AccessRightsMask | POA.InheritedAccessRightsMask) & 1)=1
)

---Get the total number of shared records
SELECT COUNT(0) FROM PrincipalObjectAccess

--Get the total number of shared records grouped by Entity
SELECT  EV.NAME AS [ENTITY NAME],COUNT(POA.OBJECTTYPECODE) AS [RECORDS COUNT]
FROM PRINCIPALOBJECTACCESS POA
INNER JOIN ENTITYLOGICALVIEW EV ON EV.OBJECTTYPECODE = POA.OBJECTTYPECODE
GROUP BY EV.NAME
ORDER BY 2 DESC

--Get the total number of shared records grouped by User
SELECT  SU.FULLNAME AS [USER NAME],COUNT(POA.OBJECTTYPECODE) AS [RECORDS COUNT]
FROM PRINCIPALOBJECTACCESS POA
INNER JOIN SYSTEMUSER SU ON POA.PRINCIPALID= SU.SYSTEMUSERID
GROUP BY SU.FULLNAME
ORDER BY 2 DESC

--Get the total number of shared records grouped by Entity and User
SELECT  SU.FULLNAME AS [USER NAME],EV.NAME AS [ENTITY NAME],COUNT(POA.OBJECTTYPECODE) AS [RECORDS COUNT]
FROM PRINCIPALOBJECTACCESS POA
INNER JOIN SYSTEMUSER SU ON POA.PRINCIPALID= SU.SYSTEMUSERID
INNER JOIN ENTITYLOGICALVIEW EV ON EV.OBJECTTYPECODE = POA.OBJECTTYPECODE
GROUP BY SU.FULLNAME,EV.NAME
ORDER BY 1

SELECT DISTINCT name, objecttypecode
FROM [CRMORGDB_MSCRM].[MetadataSchema].[Entity]
ORDER BY name ASC 

 


select distinct
       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 KYGF_MSCRM.[dbo].[PrincipalObjectAccess]            as POA
    left outer join KYGF_MSCRM.[dbo].[SystemUserBase]    as USERID
        on POA.[PrincipalId] = USERID.[SystemUserId]
    left outer join KYGF_MSCRM.[dbo].[TeamBase]          as TEAM
        on POA.[PrincipalId] = TEAM.[TeamId]
    left outer join KYGF_MSCRM.[MetadataSchema].[Entity] as ENTITY
        on POA.[ObjectTypeCode] = ENTITY.[ObjectTypeCode]
where POA.[PrincipalTypeCode] in ( 8, 9 )
      and POA.[ObjectTypeCode] = 2
      and POA.ObjectId = 'C84FBA58-8CCB-DF11-9176-02BF0AC9DF07';