Row Level Security Groups

I haven't had a chance to test this personally, but a friend sent this to me and he knows his stuff so I'm comfortable posting it here.

Row Level security information gets stored in a complicated format in the Acumatica database so it's hard to report on with SQL.

Here are two SQL Views that can help you to untangle the mystery:

CREATE OR ALTER VIEW dbo.RDPViewsUserRelationGroups AS
select
      U.CompanyID,
      U.Username,
      RG.GroupName,
      RG.Description
from Users U
      left outer join RelationGroup RG on RG.CompanyID = U.CompanyID and (
            CONVERT(BIGINT, substring(RG.GroupMask, 1, 4)) & CONVERT(BIGINT, substring(U.GroupMask, 1, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 5, 4)) & CONVERT(BIGINT, substring(U.GroupMask, 5, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 9, 4)) & CONVERT(BIGINT, substring(U.GroupMask, 9, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 13, 4)) & CONVERT(BIGINT, substring(U.GroupMask, 13, 4)) != 0
      )

CREATE OR ALTER VIEW dbo.RDPViewsCustomerRelationGroups AS
select
      BA.CompanyID,
      BA.BAccountID,
      BA.AcctCD,
      BA.AcctName,
      RG.GroupName,
      RG.Description
from Customer C
      inner join BAccount BA on BA.CompanyID = C.CompanyID and BA.BAccountID = C.BAccountID
      left outer join RelationGroup RG on RG.CompanyID = C.CompanyID and (
            CONVERT(BIGINT, substring(RG.GroupMask, 1, 4)) & CONVERT(BIGINT, substring(C.GroupMask, 1, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 5, 4)) & CONVERT(BIGINT, substring(C.GroupMask, 5, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 9, 4)) & CONVERT(BIGINT, substring(C.GroupMask, 9, 4)) != 0 or
            CONVERT(BIGINT, substring(RG.GroupMask, 13, 4)) & CONVERT(BIGINT, substring(C.GroupMask, 13, 4)) != 0
      )