UFN_SECURITY_APPUSER_GRANTED_QUERYVIEW_FORSITE_TVF

CREATE function BBDW.[UFN_SECURITY_APPUSER_GRANTED_QUERYVIEW_FORSITE_TVF]
 (  
  @APPUSERID uniqueidentifier,  
  @QUERYVIEWCATALOGID uniqueidentifier,  
  @SITEID uniqueidentifier  
 )  
 returns table  
 as   
 return  
 /*  
 Returns a record if the given user has permissions to the given QUERYVIEW  
 in a role whose SITE is either  

 1.) Blank and record security mode=0  
 2.) Assigned to the given SITE.  
 3.) Blank and record security mode=1 and the SITEID IS NULL.  

 This function is optimized for use from the Blackbaud.AppFx.Security.Catalog.SiteRecordSecurityService  
 class which implements the RecordSecurity service for Site record security.  

 As such, it assumes that a check for DENY occurs outside this function  
 and also assumes that a check for if the user is ISSYSADMIN occurs outside   
 this function.    
 */  
  select   
   1 as ISVALID  
  from   
   BBDW.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_QUERYVIEW] as q
   left join BBDW.[FACT_SITEPERMISSION] as s on s.[SYSTEMROLEAPPUSERFACTID] = q.[SYSTEMROLEAPPUSERFACTID]
  where   
   q.[APPUSERID] = @APPUSERID and   
   q.[QUERYVIEWCATALOGID] = @QUERYVIEWCATALOGID and   
   q.[GRANTORDENY] = 1 and         
   (   
    (q.[SITESECURITYMODE] = 0)  
    or  
    (s.[SITESYSTEMID] = @SITEID and (q.[SITESECURITYMODE] = 2 or q.[SITESECURITYMODE] = 3))  
    or  
    (q.[SITESECURITYMODE] = 1 and (@SITEID is null or @SITEID = '00000000-0000-0000-0000-000000000000'))          
    );