declare @assetId int = ; declare @mstrAttrTypeId int = ; declare @ownerId int = ; declare @ownerType varchar(3) = ; with groups as ( -- groups select distinct 'GRP' resourceType, g.groupId resourceId, g.ownerId resourceOwnerId, g.fsl_tablecode resourceOwnerType, g.groupName as resourceDescription, rtrim( ltrim( g.groupTypeCd )) as resourceSubType, cast( g.categoryId as int ) as resourceCategoryId, gc.category as resourceCategory from tbl_ORG_AttributeLinks c with (nolock) inner join tbl_SEC_Groups g with (nolock) on g.groupId = c.child_attrId left outer join tbl_SEC_Categories gc with (nolock) on gc.categoryId = g.categoryId and gc.ownerId = g.ownerId and gc.fsl_tablecode = g.fsl_tablecode inner join tbl_ORG_AttributeTypes t with (nolock) on t.attrTypeId = c.child_attrTypeId where c.child_attrTypeID = 23 and c.mstr_attrTypeID = @mstrAttrTypeId and c.mstr_attrID = @assetId and gc.ownerId = @ownerId and gc.fsl_tablecode = @ownerType ) , brokers as ( -- brokers select distinct 'BRO' resourceType, b.broker_Id as resourceId, null resourceOwnerId, null resourceOwnerType, b.broker_name as resourceDescription, null as resourceSubType, null as resourceCategoryId, null as resourceCategory from tbl_ORG_AttributeLinks c with (nolock) inner join tblBrokers b with (nolock) on b.broker_id = c.child_attrId inner join tbl_ORG_AttributeTypes t with (nolock) on t.attrTypeId = c.child_attrTypeId where c.child_attrTypeID = 7 and c.mstr_attrTypeID = @mstrAttrTypeId and c.mstr_attrID = @assetId ) , users as ( -- users select distinct 'USR' resourceType, m.fspro_userId resourceId, m.ownerId resourceOwnerId, m.fsl_tablecode resourceOwnerType, m.email resourceDescription, null as resourceSubType, null as resourceCategoryId, null as resourceCategory from tbl_ORG_AttributeLinks c with (nolock) inner join tbl_fspro_members m with (nolock) on m.fspro_userId = c.child_attrId inner join tbl_ORG_AttributeTypes t on t.attrTypeId = c.child_attrTypeId where c.child_attrTypeID = 34 and c.mstr_attrTypeID = @mstrAttrTypeId and c.mstr_attrID = @assetId ) , categories as ( -- categories select distinct 'CAT' resourceType, cats.attrID as resourceId, cats.ownerId resourceOwnerId, cats.fsl_TableCode resourceOwnerType, cats.attrDescription resourceDescription, null as resourceSubType, null as resourceCategoryId, null as resourceCategory from tbl_ORG_AttributeLinks c with (nolock) inner join tbl_ORG_AttributeTypes t on t.attrTypeId = c.child_attrTypeId inner join tbl_ORG_Attributes cats with (nolock) on cats.attrID = c.child_attrId where c.child_attrTypeID = 31 and c.mstr_attrTypeID = @mstrAttrTypeId and c.mstr_attrID = @assetId and cats.ownerId = @ownerId and cats.fsl_tableCode = @ownerType ) , permissions as ( select * from groups union all select * from brokers union all select * from users union all select * from categories ) #forJSON( 'permissions', attributes.datastore, 'resourceType' )#