UFN_SITESFORUSERONFEATURE

CREATE function [BBDW].[UFN_SITESFORUSERONFEATURE]
(  
@CURRENTAPPUSERID uniqueidentifier,  
@FEATUREID uniqueidentifier,  
@FEATURETYPE tinyint  
)  
returns @T table ([SITEID] uniqueidentifier)  
as  
begin   

--If the user is a syadamin, or a feature is not defined, return all sites the user can access.  
if exists (select 1 from BBDW.[DIM_APPUSER] where [APPUSERSYSTEMID] = @CURRENTAPPUSERID and [APPUSERISSYSADMIN] = 1)
 or @FEATUREID is null  
 or @FEATURETYPE is null  
 or @FEATURETYPE = 0 -- SecurityFeatureType.None  

begin  
 insert into @T ([SITEID])  
 values(null);  

 insert into @T ([SITEID])  
 select * from BBDW.[UFN_SITESFORUSER](@CURRENTAPPUSERID);       

 return       
end     

--Table to hold all relevant user/role/site relationships before   
--  removing dupes or taking security mode into consideration.  
declare @RAWSITELIST table(  
 [SITESECURITYMODE] tinyint,  
 [SITEID] uniqueidentifier  
);  

if @FEATURETYPE = 1  -- SecurityFeatureType.Form
begin
  insert into @RAWSITELIST(
    [SITESECURITYMODE],
    [SITEID]
  )
  select 
    ds.[SITESECURITYMODE], 
    sp.[SITESYSTEMID] as [SITEID]
  from BBDW.[v_SECURITY_SYSTEMROLEASSIGNMENT_USER_FORM] ds
  left join BBDW.[FACT_SITEPERMISSION] sp on sp.[SYSTEMROLEAPPUSERFACTID] = ds.[SYSTEMROLEAPPUSERFACTID]
  where 
  ds.[APPUSERID] = @CURRENTAPPUSERID and
  ds.[DATAFORMINSTANCECATALOGID] = @FEATUREID;
end

if @FEATURETYPE = 2  -- SecurityFeatureType.DataList
begin
  insert into @RAWSITELIST(
    [SITESECURITYMODE],
    [SITEID]
  )
  select 
    ds.[SITESECURITYMODE], 
    sp.[SITESYSTEMID] as [SITEID]
  from BBDW.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_DATALIST] ds
  left join BBDW.[FACT_SITEPERMISSION] sp on sp.[SYSTEMROLEAPPUSERFACTID] = ds.[SYSTEMROLEAPPUSERFACTID]
  where 
  ds.[APPUSERID] = @CURRENTAPPUSERID and
  ds.[DATALISTCATALOGID] = @FEATUREID;
end

if @FEATURETYPE = 10  -- SecurityFeatureType.AdHocQueryView  
begin  
 insert into @RAWSITELIST(  
  [SITESECURITYMODE],
  [SITEID]  
 )  
 select   
  sq.[SITESECURITYMODE],
  sp.[SITESYSTEMID] as [SITEID]  
 from BBDW.[v_SECURITY_SYSTEMROLEASSIGNMENT_USER_QUERYVIEW] sq
 left join BBDW.[FACT_SITEPERMISSION] sp on sp.[SYSTEMROLEAPPUSERFACTID] = sq.[SYSTEMROLEAPPUSERFACTID]
 where   
 sq.[APPUSERID] = @CURRENTAPPUSERID and
 sq.[QUERYVIEWCATALOGID] = @FEATUREID;  
end  

--If the raw lists contains a role assignment that allows access to all records, return all sites.  
if exists( select 1 from @RAWSITELIST where [SITESECURITYMODE] = 0)  
begin  
 insert into @T ([SITEID])  
 values(null);  

 insert into @T ([SITEID])  
 select * from BBDW.[UFN_SITESFORUSER](@CURRENTAPPUSERID);  
end  
else --Otherwise, return the listed sites (null for role assignments for records with no sites).  
begin  
 insert into @T ([SITEID])  
 select distinct case [SITESECURITYMODE] when 1 then null else [SITEID] end from @RAWSITELIST;  
end  

return;  
end;