UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYFOLDER_IN_SYSTEMROLE

CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYFOLDER_IN_SYSTEMROLE
(
    @APPUSERID uniqueidentifier,
    @ADHOCQUERYFOLDERID uniqueidentifier
)
  returns bit
as
  /*
  Returns true if user has been granted and not denied permission to edit the ad-hoc query folder for a System Role.
  */
begin
    --If at least one grant and no deny then return true

    --otherwise, false

    declare @GRANT bit;
    set @GRANT = 0;

    -- note that this routine assumes the check for SysAdmin has been performed already


    -- check to see if the query has been granted to everyone

    declare @SECURITYLEVEL tinyint;
    declare @OWNERID uniqueidentifier;

    set @SECURITYLEVEL = 0; 
    select 
        @SECURITYLEVEL = SECURITYLEVELCODE,
        @OWNERID = OWNERID
    from dbo.ADHOCQUERYFOLDER (nolock) where ID = @ADHOCQUERYFOLDERID;

    if (@OWNERID = @APPUSERID) or (@SECURITYLEVEL = 0) -- All users

        set @GRANT = 1;

    -- the query folder has not been granted to everyone; check to see if the user has been 

    -- granted (and not denied) explicit rights to the ad-hoc query folder.

    if @GRANT = 0
    begin
        --order by GRANTORDENY, deny will be first.

        select top 1 
            @GRANT = SECURITYVIEWFOLDER.GRANTORDENY
        from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_ADHOCQUERYFOLDER (nolock) SECURITYVIEWFOLDER
        inner join dbo.ADHOCQUERYFOLDER (nolock) on SECURITYVIEWFOLDER.ADHOCQUERYFOLDERID = ADHOCQUERYFOLDER.ID
        where 
            (SECURITYVIEWFOLDER.APPUSERID = @APPUSERID) and 
            (SECURITYVIEWFOLDER.ADHOCQUERYFOLDERID = @ADHOCQUERYFOLDERID)
        order by 
        SECURITYVIEWFOLDER.GRANTORDENY asc;
    end;

    return @GRANT;
end