USP_SIMPLEDATALIST_SOURCESWITHQUERIES

CREATE procedure dbo.[USP_SIMPLEDATALIST_SOURCESWITHQUERIES](@CURRENTAPPUSERID uniqueidentifier)
as
set nocount on;

declare @ISSYSADMIN bit;
declare @HASQUERYVIEWSYSTEMPRIVILEGE bit = 0;

declare @OKVIEWS table ([OBJECTNAME] nvarchar(128));
declare @SMARTCATALOGITEMS table ([SMARTQUERYCATALOGID] uniqueidentifier);    

select @ISSYSADMIN = [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID;

if @ISSYSADMIN = 0
  select @HASQUERYVIEWSYSTEMPRIVILEGE = dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, '5f9bbcaf-9c67-4539-9880-ae4f709a9c1f');

/* Filter out any ad-hoc queries involving query views to which the current user does not have rights */
if (@ISSYSADMIN = 0 and @HASQUERYVIEWSYSTEMPRIVILEGE = 0)
  begin

    insert into @OKVIEWS ([OBJECTNAME])
      select 
        [QUERYVIEWCATALOG].[OBJECTNAME] 
      from dbo.[QUERYVIEWCATALOG]
      where [ROOTOBJECT] = 1 and
      exists (
        select 
          [OKVIEWS].[QUERYVIEWCATALOGID]
        from dbo.[UFN_SECURITY_GETGRANTEDQUERYVIEWSFORUSER](@CURRENTAPPUSERID) as [OKVIEWS] 
        where [OKVIEWS].[QUERYVIEWCATALOGID] = [QUERYVIEWCATALOG].[ID]);
  end;

if @ISSYSADMIN = 0

  begin

    insert into @SMARTCATALOGITEMS ([SMARTQUERYCATALOGID])

      select
        [SMARTQUERYCATALOG].[ID]
      from dbo.[SMARTQUERYCATALOG]
      where
      exists
      (
      select 
        [SMARTCATALOGITEMS].[SMARTQUERYCATALOGID]
      from dbo.[UFN_SECURITY_GETGRANTEDSMARTQUERIESFORUSER](@CURRENTAPPUSERID) as [SMARTCATALOGITEMS] where [SMARTCATALOGITEMS].[SMARTQUERYCATALOGID] = [SMARTQUERYCATALOG].[ID]
      );
  end


begin

  select
    distinct
    [VALUE],
    [LABEL]
  from
  (
  select 
    [QUERYVIEWCATALOG].[ID] as [VALUE],
    [QUERYVIEWCATALOG].[DISPLAYNAME] as [LABEL]
  from dbo.[QUERYVIEWCATALOG] 
  inner join dbo.[ADHOCQUERY] on [QUERYVIEWCATALOG].[ID] = [ADHOCQUERY].[QUERYVIEWCATALOGID]
  where [QUERYVIEWCATALOG].[ROOTOBJECT] = 1 and
  /* Filter out restricted query views */
  ((@ISSYSADMIN = 1 or @HASQUERYVIEWSYSTEMPRIVILEGE = 1)
  /* This will return a list of query views in use by the ad-hoc query but not in the OK views table variable;
  These ad-hoc queries should not be returned to the client. */
  or not exists(
    select 
      [QUERYVIEWSINUSE].[OBJECTNAME]
    from dbo.[UFN_ADHOCQUERY_QUERYVIEWSINUSE]([ADHOCQUERY].[QUERYDEFINITIONXML]) as [QUERYVIEWSINUSE]
    where not exists (
    select [OKVIEWS].[OBJECTNAME]
    from @OKVIEWS as [OKVIEWS] where [OKVIEWS].[OBJECTNAME] = [QUERYVIEWSINUSE].[OBJECTNAME])))
    /* End restricted query views filter */

    union

    select
    [SMARTQUERYCATALOG].[ID] as [VALUE],
        [SMARTQUERYCATALOG].[NAME] as [LABEL]
    from dbo.[SMARTQUERYCATALOG] 
    inner join dbo.[SMARTQUERYINSTANCE] on [SMARTQUERYCATALOG].[ID] = [SMARTQUERYINSTANCE].[SMARTQUERYCATALOGID]
  where @ISSYSADMIN = 1
  or
  exists (
    select 
      [SMARTCATALOGITEMS].[SMARTQUERYCATALOGID]
    from @SMARTCATALOGITEMS as [SMARTCATALOGITEMS]
    where [SMARTCATALOGITEMS].[SMARTQUERYCATALOGID] = [SMARTQUERYCATALOG].[ID])
    and
    (dbo.[UFN_SECURITY_APPUSER_GRANTED_SMARTQUERYINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [SMARTQUERYINSTANCE].[ID]) = 1)

  ) as [QUERIES]

  order by [LABEL];

end