Sql query - SCCM rba "GrantedOperations"

Hello fellow sccm admin,

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

After some work here are a simple query:

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'

and the result look like this:

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

My problem is with the "calc" column value which is a sum of all bitflag. In that exemple, this group have all the rights possible; because 1024+16+4+2+1=1047

But when some user have more exotic rights, the numbers goes boom. Below a short exemple :

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

1827831463 is the sum of many bitflag but how can i know which "operationname" is refering to? In the best possible way, i would like a simple yes/no or true/false like with a CASE statement

Solution

If I understand you correctly you want to check the GrantedOperations for which Operations are set.

In this case this is not simply a sum (although it is) but the Operations are each chosen as powers of two (Read is 2^0, Write 2^1, Create 2^10) which means if you view the GrantedOperations in binary form, each operation set is a different bit flipped from 0 to 1.

Because of this to check the presence of any Operation you can use a binary AND operator with the operation (which will only have one bit set) and if the result is the number of the operation then it means it is set. (This whole Bitflag powers of 2 thing is a very old programming techniques to store settings in integers so you can probably find a lot of articles explaining in detail the advantages if you want).

So this means you can write the query like this:

select 
    RoleName,
    ObjectTypeName,
    GrantedOperations,
    BitFlag,
    CASE GrantedOperations & BitFlag
      WHEN BitFlag THEN 1
      ELSE 0
    END OperationGranted,
    OperationName
from vRBAC_AdminRoles ad
join RBAC_RoleOperations ro on ro.RoleID = ad.RoleID
join vRBAC_AvailableOperations av on av.ObjectTypeID = ro.ObjectTypeID

and it should lead to the result you wanted.