UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_IN_NONRACROLE

create function BBDW.[UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_IN_NONRACROLE]

(
@APPUSERID uniqueidentifier,
@SMARTQUERYCATALOGID uniqueidentifier
)

returns bit as

/*
Returns true if the given user has permissions to the given smart query
in a role whose security group is blank
This function is optimized for use from a smart query TVF.
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.  
*/


begin

--If user granted permission to the feature in a role with no ringfence then 
--the user has permission regardless of the record in question.

if exists

    (
        select 
            1 
        from 
            BBDW.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY] as SV
        where 
            SV.[APPUSERID] = @APPUSERID and 
            SV.[SMARTQUERYCATALOGID] = @SMARTQUERYCATALOGID and 
            SV.[GRANTORDENY] = 1 and 
            SV.[CONSTITUENTSECURITYMODE] = 0
    )

    return 1;

return  0;

end;