UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORSITE_TVF

create function BBDW.[UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORSITE_TVF]
(
  @APPUSERID uniqueidentifier,
  @SMARTQUERYCATALOGID uniqueidentifier,
  @SITEID uniqueidentifier
)
returns table

as return

/*
Returns a record if the given user has permissions to the given SMARTQUERY
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_SMARTQUERY] as [SECURITYVIEW]
    left join BBDW.[FACT_SITEPERMISSION] on [FACT_SITEPERMISSION].[APPUSERDIMID] = [SECURITYVIEW].[APPUSERDIMID] and [FACT_SITEPERMISSION].[SYSTEMROLEDIMID] = [SECURITYVIEW].[SYSTEMROLEDIMID]
  where 
    [SECURITYVIEW].[APPUSERID] = @APPUSERID and 
    [SECURITYVIEW].[SMARTQUERYCATALOGID] = @SMARTQUERYCATALOGID and 
    [SECURITYVIEW].[GRANTORDENY] = 1 and
    (    
      ([SECURITYVIEW].[SITESECURITYMODE] = 0)
      or
      ([FACT_SITEPERMISSION].[SITESYSTEMID] = @SITEID and ([SECURITYVIEW].[SITESECURITYMODE] = 2 or [SECURITYVIEW].[SITESECURITYMODE] = 3))
      or
      ([SECURITYVIEW].[SITESECURITYMODE] = 1 and @SITEID is null)                                
    )