UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORSITE_TVF

create function dbo.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 
    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY as SECURITYVIEW
    left join dbo.SITEPERMISSION on SITEPERMISSION.APPUSERID = SECURITYVIEW.APPUSERID and SITEPERMISSION.SYSTEMROLEID = SECURITYVIEW.SYSTEMROLEID
  where 
    SECURITYVIEW.APPUSERID = @APPUSERID and 
    SECURITYVIEW.SMARTQUERYCATALOGID = @SMARTQUERYCATALOGID and 
    SECURITYVIEW.GRANTORDENY = 1 and
    (    
      (SECURITYVIEW.SITESECURITYMODE = 0)
      or
      (SITEPERMISSION.SITEID = @SITEID and (SECURITYVIEW.SITESECURITYMODE = 2 or SECURITYVIEW.SITESECURITYMODE = 3))
      or
      (SECURITYVIEW.SITESECURITYMODE = 1 and @SITEID is null)                                
    )