UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCEEDIT_IN_SYSTEMROLE

CREATE function dbo.UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCEEDIT_IN_SYSTEMROLE
(
  @APPUSERID uniqueidentifier,
    @ADHOCQUERYID uniqueidentifier
)
  returns bit
as
  /*
  Returns true if user has been granted and not denied permission to edit 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 @SECURITYLEVELEDIT tinyint;
    declare @SITEID uniqueidentifier;

    set @SECURITYLEVEL = 0;
    set @SECURITYLEVELEDIT = 0;
    select 
        @OWNERID = OWNERID, 
        @SECURITYLEVEL = SECURITYLEVEL, 
        @SECURITYLEVELEDIT = SECURITYLEVELEDIT, 
        @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 (@SECURITYLEVELEDIT = 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 edit rights to the ad-hoc query instance.

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

        select top 1 
            @GRANT = SECURITYVIEWEDIT.GRANTORDENY
        from dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_ADHOCQUERYINSTANCEEDIT (nolock) SECURITYVIEWEDIT
        inner join dbo.ADHOCQUERY (nolock) on SECURITYVIEWEDIT.ADHOCQUERYID = ADHOCQUERY.ID
        left join dbo.SYSTEMROLEPERM_ADHOCQUERYINSTANCE (nolock) on 
            ADHOCQUERY.SECURITYLEVEL <> 0 and -- No need to check SYSTEMROLEPERM_ADHOCQUERYINSTANCE if run permissions is GRANTed to all users

            SECURITYVIEWEDIT.SYSTEMROLEID = SYSTEMROLEPERM_ADHOCQUERYINSTANCE.SYSTEMROLEID and
            SECURITYVIEWEDIT.ADHOCQUERYID = SYSTEMROLEPERM_ADHOCQUERYINSTANCE.ADHOCQUERYID and
            SYSTEMROLEPERM_ADHOCQUERYINSTANCE.GRANTORDENY = 1
        where 
            (SECURITYVIEWEDIT.APPUSERID = @APPUSERID) and 
            (SECURITYVIEWEDIT.ADHOCQUERYID = @ADHOCQUERYID) and
            (
                -- Verify that either all roles can run this query or this role is granted run permission

                ADHOCQUERY.SECURITYLEVEL = 0 or -- All users

                SYSTEMROLEPERM_ADHOCQUERYINSTANCE.ID is not null
            )                        
        order by 
          SECURITYVIEWEDIT.GRANTORDENY asc;
    end;

    return @GRANT;
end