UFN_ADHOCQUERY_APPLYQUERYVIEWSECURITY

create function dbo.[UFN_ADHOCQUERY_APPLYQUERYVIEWSECURITY]
(
  @CURRENTAPPUSERID uniqueidentifier
)
returns table
as return (
  with xmlnamespaces (
    'bb_appfx_queryview' as ns, 'bb_appfx_commontypes' as common
  ), [ADHOCQUERIESWITHDENIEDQUERYVIEWS] ([ID]) as (
    select
      [ADHOCQUERY].[ID]
    from dbo.[ADHOCQUERY]
    outer apply dbo.[UFN_ADHOCQUERY_QUERYVIEWSINUSE]([ADHOCQUERY].[QUERYDEFINITIONXML]) as [QUERYVIEWSINUSE]
    inner join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[OBJECTNAME] = [QUERYVIEWSINUSE].[OBJECTNAME]
    left outer join dbo.[UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER](@CURRENTAPPUSERID) as [GRANTEDQUERYVIEWS] on [GRANTEDQUERYVIEWS].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]
    inner join dbo.[APPUSER] on [APPUSER].[ID] = @CURRENTAPPUSERID
    where [APPUSER].[ISSYSADMIN] = 0
      and dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, '5f9bbcaf-9c67-4539-9880-ae4f709a9c1f') = 0
      and [GRANTEDQUERYVIEWS].[QUERYVIEWCATALOGID] is null
  )
  select
    [ADHOCQUERY].[ID]
  from dbo.[ADHOCQUERY]
  where not exists (select top 1 1 from [ADHOCQUERIESWITHDENIEDQUERYVIEWS] where [ID] = [ADHOCQUERY].[ID])
);