UFN_EXPORTDEFINITION_APPLYQUERYVIEWSECURITY

create function dbo.[UFN_EXPORTDEFINITION_APPLYQUERYVIEWSECURITY]
(
  @CURRENTAPPUSERID uniqueidentifier
)
returns table
as return (
  with xmlnamespaces (
    'bb_appfx_queryview' as ns, 'bb_appfx_commontypes' as common
  ), [EXPORTDEFINITIONSWITHDENIEDQUERYVIEWS] ([ID]) as (
    select
      [EXPORTDEFINITION].[ID]
    from dbo.[EXPORTDEFINITION]
    outer apply dbo.[UFN_EXPORTDEFINITION_QUERYVIEWSINUSE]([EXPORTDEFINITION].[EXPORTDEFINITIONXML]) 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
      -- query views where SecurityUIDisplayFeatire = False and UseForExportDefinitionsOnly = True are permitted for everyone

      and not (coalesce([QUERYVIEWCATALOG].[QUERYVIEWSPEC].value('(ns:QueryViewSpec/@SecurityUIDisplayFeature)[1]', 'bit'), 1) = 0
               and
               coalesce([QUERYVIEWCATALOG].[QUERYVIEWSPEC].value('(ns:QueryViewSpec/@UseForExportDefinitionsOnly)[1]', 'bit'), 0) = 1)
  )
  select
    [EXPORTDEFINITION].[ID]
  from dbo.[EXPORTDEFINITION]
  where not exists (select top 1 1 from [EXPORTDEFINITIONSWITHDENIEDQUERYVIEWS] where [ID] = [EXPORTDEFINITION].[ID])
);