Friday, September 30, 2011

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'
    '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))
    --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.


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

Post a Comment