UFN_ADHOCQUERY_GETUSERQUERIES

CREATE function dbo.[UFN_ADHOCQUERY_GETUSERQUERIES]
(
  @CURRENTAPPUSERID uniqueidentifier
)
returns table
as return
  with xmlnamespaces ('Blackbaud.AppFx.WebService.API.1' as [ns])
  select
    [QUERY].[ID]
  from dbo.[ADHOCQUERY] as [QUERY]
  left outer join dbo.[V_SECURITY_SYSTEMROLEASSIGNMENT_USER_ADHOCQUERYINSTANCE] as [QUERYSECURITY] on
    [QUERY].[ID] = [QUERYSECURITY].[ADHOCQUERYID] and [QUERYSECURITY].[APPUSERID] = @CURRENTAPPUSERID
  where
    (
      [QUERYSECURITY].[GRANTORDENY] = 1     -- the user's role has been granted access to the ad-hoc query

      or
      [QUERY].[OWNERID] = @CURRENTAPPUSERID -- the user owns the ad-hoc query

      or
      [QUERY].[SECURITYLEVEL] = 0           -- the query is set to be runnable by all users

    )
    -- AND the user has access to ALL of the query views that make up the ad-hoc query

    -- (implemented as: there is NO query view in the ad-hoc query to which the user does NOT have access)

    and not exists (
      select
        top 1 1
      from
      (
        -- this gathers up all of the object names from all of the nodes used in the ad-hoc query

        select
          [QUERYVIEWCATALOG].[ID] as [QUERYVIEWCATALOGID]
        from dbo.[ADHOCQUERY]
        cross apply [QUERY].[QUERYDEFINITIONXML].nodes('ns:AdHocQuery/*/ns:f[@ObjectName!='''']') as T(c)
        -- inner join to QUERYVIEWCATALOG to go from OBJECTNAME to ID

        inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[OBJECTNAME] = T.c.value('@ObjectName', 'nvarchar(128)')
        where [ADHOCQUERY].[ID] = [QUERY].[ID] 

        union all

        -- this makes sure we get the object name for the root query view upon which the ad-hoc query is based

        -- (there might not be any fields selected for the root query view, so it might not show up in the previous statement, but it still counts)

        select
          [ADHOCQUERY].[QUERYVIEWCATALOGID]
        from dbo.[ADHOCQUERY]
        where [ADHOCQUERY].[ID] = [QUERY].[ID]
      ) as [QUERYVIEWS]
      -- left outer join to this to identify any query views to which the user does NOT have access

      left outer join dbo.[UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER](@CURRENTAPPUSERID) as [GRANTEDVIEWS] on [GRANTEDVIEWS].[QUERYVIEWCATALOGID] = [QUERYVIEWS].[QUERYVIEWCATALOGID]
      where [GRANTEDVIEWS].[QUERYVIEWCATALOGID] is null -- this identifies any query views to which the user does NOT have access

    )