UFN_SECURITY_APPUSER_GRANTED_SITEIDS_FORQUERYVIEW

create function dbo.UFN_SECURITY_APPUSER_GRANTED_SITEIDS_FORQUERYVIEW
(
@APPUSERID uniqueidentifier,
@QUERYVIEWID uniqueidentifier
)
returns TABLE as

/*
Returns a row for every site that the the user has rights to according to record access security.


This function is optimized for use from the Blackbaud.AppFx.Security.Catalog.SiteRecordSecurityService
class which implements the RecordSecurity service for site 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.  

It also assumes a check for UFN_SECURITY_APPUSER_GRANTED_QUERYVIEW_IN_NONSITEROLE 
occurs outside this function. If that function returns true there is no need to join to this TVF.

*/

RETURN

(

select CSAA.CONSTITUENTID AS ID from 
dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT as CSAA
WHERE
CSAA.CONSTIT_SECURITY_ATTRIBUTEID IN 
            (
                    select SRCSA.CONSTIT_SECURITY_ATTRIBUTEID

                    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_QUERYVIEW as SV

                    inner join dbo.SYSTEMROLE_CONSTIT_SECURITY_ATTRIBUTE as SRCSA
                    ON SV.SYSTEMROLEID=SRCSA.ID

                    WHERE

                    SV.APPUSERID=@APPUSERID 
                    AND SV.QUERYVIEWCATALOGID=@QUERYVIEWID
                    AND SV.GRANTORDENY=1
                )

UNION ALL

--Constits with no security attributes if the user in a role with security mode = 1

select ID from dbo.CONSTITUENT

WHERE EXISTS
    (
        select 1

        from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_QUERYVIEW as SV


        WHERE

        SV.APPUSERID=@APPUSERID 
        AND SV.QUERYVIEWCATALOGID=@QUERYVIEWID
        AND SV.GRANTORDENY=1
        AND SV.RECORDSECURITYMODE=1
        AND SV.SYSTEMROLEID NOT IN (SELECT ID FROM SYSTEMROLE_CONSTIT_SECURITY_ATTRIBUTE)

    )

    AND

    ID NOT IN

    (
    select CSAA.CONSTITUENTID from 
    dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT as CSAA
    )

)