Monday 3 February 2014

List privileges for security roles in Microsoft Dynamics CRM 2011/2013

So a couple of weeks back I was asked to disable export to excel functionality for all users, unfortunately nobody was sure which roles had the Export To Excel privilege (prvExportToExcel) so I used this query to find out:

select r.name as Role, x.label,p.Name,
case p.accessright -- technically a bit field, but CRM treats as an int
when 1 then 'Read'
when 2 then 'Write'
when 4 then 'Append'
when 16 then 'AppendTo'
when 32 then 'Create'
when 65536 then 'Delete'
when 262144 then 'Share'
when 524288 then 'Assign'
else '?' end as Permission,

case rp.PrivilegeDepthMask -- technically a bit field, but CRM treats as an int
when 1 then 'User'
when 2 then 'BusinessUnit'
when 4 then 'Parent:Child'
when 8 then 'Organisation'
else '?' end as Scope
from role r

left join RolePrivileges rp on r.roleid = rp.roleid
left join Privilege p on rp.privilegeid = p.privilegeid
left join PrivilegeObjectTypeCodes potc on p.privilegeid = potc.privilegeid
left join MetadataSchema.Entity e on potc.objecttypecode = e.objecttypecode
left join (select max(overwritetime) as owt, label, localizedlabelid, objectid, objectcolumnname from 

MetadataSchema.LocalizedLabel group by localizedlabelid, label, objectid, objectcolumnname) as x on e.entityid= x.objectid and x.objectcolumnname = 'LocalizedName'

where p.name like 'prvExportToExcel'
order by r.name, e.logicalname, 3, 4

The left joins are necessary to ensure that the On/Off privileges like Export To Excel are included. This was the result.

No comments:

Post a Comment