UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCE_IN_SYSTEMROLE

CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCE_IN_SYSTEMROLE
(
  @APPUSERID uniqueidentifier,
    @ADHOCQUERYID uniqueidentifier
)
  returns bit
as
  /*
  Returns true if user has been granted and not denied the ad-hoc query instance 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 user is the ower of the query, or the query has been GRANTed to everyone

        declare @OWNERID uniqueidentifier;
        declare @SECURITYLEVEL tinyint;
        declare @SITEID uniqueidentifier;

        set @SECURITYLEVEL = 0;
        select @OWNERID = OWNERID, @SECURITYLEVEL = SECURITYLEVEL, @SITEID = SITEID from dbo.ADHOCQUERY (nolock) where ID = @ADHOCQUERYID;

        if (@SITEID is not null) and (dbo.UFN_SITEALLOWEDFORUSER(@APPUSERID, @SITEID) <> 1)
            return 0;

        if (@OWNERID = @APPUSERID) or (@SECURITYLEVEL = 0)
            set @GRANT = 1;   

        -- user isn't the owner, and the query 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 instance.

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

                    select top 1 
                    @GRANT = SECURITYVIEW.GRANTORDENY
                    from 
                    dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_ADHOCQUERYINSTANCE (nolock) SECURITYVIEW
                    where 
                    (SECURITYVIEW.APPUSERID = @APPUSERID) and (SECURITYVIEW.ADHOCQUERYID = @ADHOCQUERYID)
                    order by 
                    SECURITYVIEW.GRANTORDENY asc;
            end;

        return @GRANT;
end