title: Sql query - SCCM rba "GrantedOperations"
body: <p>Hello fellow sccm admin,</p>
  <p>I wanted to make a report in ssrs to quickly find what permissions a group or user have in sccm.
  It's also for the purpose to put it in excel format and documentation</p>
  <p>After some work here are a simple query:</p>
  <pre><code>select RoleName,ObjectTypeName,GrantedOperations,BitFlag,OperationName
  from vRBAC_AdminRoles ad
  join RBAC_RoleOperations ro on ro.RoleID = ad.RoleID
  join vRBAC_AvailableOperations av on av.ObjectTypeID = ro.ObjectTypeID
  where RoleName = '[NEW] - Exploitation N1 - 163 SCCM Exploitation N1'
  </code></pre>
  <p>and the result look like this:</p>
  <pre><code>RoleName    ObjectTypeName                          calc BitFlag    OperationName
  ARO - test security role Query  SMS_Subscription    1047    1       Read
  ARO - test security role Query  SMS_Subscription    1047    2       Modify
  ARO - test security role Query  SMS_Subscription    1047    4       Delete
  ARO - test security role Query  SMS_Subscription    1047    16      Set Security Scope
  ARO - test security role Query  SMS_Subscription    1047    1024    Create
  </code></pre>
  <p>My problem is with the <em>&quot;calc&quot;</em> column value which is a sum of all <em>bitflag</em>. In that exemple, this group have all the rights possible; because 1024+16+4+2+1=1047</p>
  <p>But when some user have more exotic rights, the numbers goes boom. Below a short exemple :</p>
  <pre><code>RoleName    ObjectTypeName  calc    BitFlag OperationName
  AF - ETI    SMS_Collection  1827831463  1   Read
  AF - ETI    SMS_Collection  1827831463  2   Modify
  AF - ETI    SMS_Collection  1827831463  4   Delete
  AF - ETI    SMS_Collection  1827831463  8   Read BitLocker Recovery Key
  AF - ETI    SMS_Collection  1827831463  16  Rotate BitLocker Recovery Key
  AF - ETI    SMS_Collection  1827831463  32  Remote Control
  AF - ETI    SMS_Collection  1827831463  64  Run CMPivot
  AF - ETI    SMS_Collection  1827831463  128 Modify Resource
  AF - ETI    SMS_Collection  1827831463  512 Delete Resource
  AF - ETI    SMS_Collection  1827831463  1024    Create
  </code></pre>
  <p><strong>1827831463</strong> is the sum of many <em>bitflag</em> but how can i know which <em>&quot;operationname&quot;</em> is refering to?
  In the best possible way, i would like a simple yes/no or true/false like with a <strong>CASE</strong> statement</p>