UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER

CREATE function dbo.UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER(@APPUSERID uniqueidentifier)
returns table

as

--if the Appuser is a sysadmin this function should not be used, just select ID from queryviewcatalog


return (

    with assignedperms as
    (
    select
    SRP.QUERYVIEWCATALOGID as FEATUREID,
    SRP.GRANTORDENY
    from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_QUERYVIEW as SRP
    where SRP.APPUSERID=@APPUSERID
    )

select DISTINCT FEATUREID as QUERYVIEWCATALOGID from assignedperms
where GRANTORDENY=1 
and FEATUREID not in 
    --exclude explicitly denied query views in system roles

    (
        select FEATUREID from assignedperms where (GRANTORDENY=0)
    )

)