UFN_SITESFORUSER

create function BBDW.[UFN_SITESFORUSER]
(  
@CURRENTAPPUSERID uniqueidentifier  
)  
returns @T table ([SITEID] uniqueidentifier primary key)  
as  
begin   
/*Return all sites if the application user is a system administrator or is assigned to a role with access to all sites*/  
if exists (select 1 from BBDW.[DIM_APPUSER] where [APPUSERSYSTEMID] = @CURRENTAPPUSERID and [APPUSERISSYSADMIN] = 1)
or exists(SELECT [SYSTEMROLEAPPUSERFACTID] from BBDW.[FACT_SYSTEMROLEAPPUSER] where [APPUSERSYSTEMID] = @CURRENTAPPUSERID and [SITESECURITYMODE] = 0) begin  
 insert into @T(SITEID)   
  select [SITESYSTEMID] as [SITEID] from BBDW.[DIM_SITE];        

 return       
end      

insert into @T(SITEID)  
 select distinct   
  sp.[SITESYSTEMID] as [SITEID]
 from   
  BBDW.[FACT_SITEPERMISSION] sp
  inner join BBDW.[DIM_APPUSER] a on sp.[APPUSERDIMID] = a.[APPUSERDIMID]
 where   
  a.[APPUSERSYSTEMID] = @CURRENTAPPUSERID;       
return   

end;