select cte.group_identity,cte.member_identity,cte.lv,
 USR.user_name, gp.NAME,APL.APPLICATION_ID
from 
cte
left join [SharedServices].[dbo].[V_all_users] USR on USR.SID=cte.member_identity
left join SharedServices.dbo.CSS_GROUPS GP on gp.IDENTITY_ID=cte.group_identity
left join [SharedServices].[dbo].[CSS_PROVISIONING_INFO] APL on APL.MEMBER_IDENTITY=cte.group_identity
where usr.user_name like 'ab%' order by lv