MSCRM – User privileges/access rights “report” script

This is again about MSCRM 3.0 and not sure that would be the same with higher versions. Without further ado, here it is:

A script corresponds to the scenario/constraints I currently face, so in your case you might need to tweak it.

select distinct

    'Right' = case 

    WHEN pb.AccessRight = 1 THEN 'Read'

    WHEN pb.AccessRight = 2 THEN 'Write'

    WHEN pb.AccessRight = 4 THEN 'Append'

    WHEN pb.AccessRight = 16 THEN 'Append To'

    WHEN pb.AccessRight = 32 THEN 'Create'

    WHEN pb.AccessRight = 65536 THEN 'Delete'

    WHEN pb.AccessRight = 262144 THEN 'Share'

    WHEN pb.AccessRight = 524288 THEN 'Assign'

    end, 

 rb.Name, 

    'Scope' = case 

    WHEN rp.PrivilegeDepthMask = 1 THEN 'Own Only'

    WHEN rp.PrivilegeDepthMask = 2 THEN 'Business Unit'

    WHEN rp.PrivilegeDepthMask = 4 THEN 'Parent->Child'        

    WHEN rp.PrivilegeDepthMask = 8 THEN 'Organization'

    WHEN rp.PrivilegeDepthMask = 16 THEN 'Own Only - Inherited'

    WHEN rp.PrivilegeDepthMask = 32 THEN 'Business Unit - Inherited'

    WHEN rp.PrivilegeDepthMask = 64 THEN 'Parent->Child - Inherited'

    WHEN rp.PrivilegeDepthMask = 128 THEN 'Organization - Inherited'

    ELSE cast(rp.PrivilegeDepthMask as varchar(10))

    end,

    pb.Name

    --rp.PrivilegeDepthMask, pb.AccessRight, bub.Name --,*

    from PrivilegeBase pb

    join RolePrivileges rp on rp.PrivilegeId = pb.PrivilegeId

    join RoleBase rb on rb.RoleId = rp.RoleId

    join BusinessUnitBase bub on bub.BusinessUnitId = rb.BusinessUnitId

    where pb.Name like '%activity' and rp.PrivilegeDepthMask NOT IN (16, 32, 64, 128)

    order by pb.Name, rb.Name --, bub.Name

Few notes.

PrivilegeDepthMask of 16 and 128 is x*2^4, what happens (as I believe) when privilege is inherited from the organization level role to a business unit (I might be wrong, better verify yourself :). In my case inherited privileges are always the same, that’s why that rp.PrivilegeDepthMask NOT IN (16, 128) at the end. I also filter by entity name in a hacky way, you might want to join entities table/view in your case.

This is it.

Links:
http://mscrmking.blogspot.com/2010/10/explination-of-accessright-and.html

[Update: 13-Oct-2011, script updated with parent to child scenarios]