Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-itshop-autopublish-adsgroup-flags-db-evidence.md

> Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-itshop-autopublish-adsgroup-flags-db-evidence.md

Sandbox DB Evidence — AutoPublish ADSGroup + IT Shop flags (2026-04-27)

Goal: capture live DB evidence explaining why AD groups (ADSGroup) are not requestable in this sandbox yet, and which DB objects enforce IT Shop flag rules.

Method

Evidence

1) Which tables have IsForITShop / IsITShopOnly flags?

Query (filtered to common tables):

WITH t AS (
  SELECT s.name AS schemaName, o.name AS tableName, c.name AS columnName
  FROM sys.columns c
  JOIN sys.objects o ON o.object_id = c.object_id
  JOIN sys.schemas s ON s.schema_id = o.schema_id
  WHERE o.type IN ('U','V')
)
SELECT schemaName, tableName, columnName
FROM t
WHERE tableName IN ('ADSGroup','ESet','AccProduct','AccProductGroup','QERReuse','QERAssign','QERResource')
  AND (columnName LIKE '%ITShop%' OR columnName LIKE '%ForITShop%')
ORDER BY tableName, columnName;

Observed rows:

2) ADSGroup is not IT Shop-enabled in this sandbox (yet)

Query:

SELECT IsForITShop, IsITShopOnly, COUNT(*) AS cnt
FROM dbo.ADSGroup
GROUP BY IsForITShop, IsITShopOnly
ORDER BY cnt DESC;

Observed:

Defaults on ADSGroup (schema metadata):

SELECT c.name AS columnName, dc.definition AS defaultExpr
FROM sys.columns c
LEFT JOIN sys.default_constraints dc
  ON dc.parent_object_id=c.object_id AND dc.parent_column_id=c.column_id
WHERE c.object_id=OBJECT_ID('dbo.ADSGroup')
  AND c.name IN ('IsForITShop','IsITShopOnly','UID_AccProduct');

Observed:

3) AutoPublish parameter exists, but ADSGroup is disabled (DialogConfigParm)

Query:

SELECT FullPath, Value, Enabled, IsCrypted, DisplayName
FROM dbo.DialogConfigParm
WHERE FullPath LIKE '%ITShop%'
  AND FullPath LIKE '%AutoPublish%'
ORDER BY FullPath;

Observed:

FullPathValueEnabled
QER\ITShop\AutoPublish11
QER\ITShop\AutoPublish\ADSGroup10
QER\ITShop\AutoPublish\ADSGroup\AutoFillDisplayName10
QER\ITShop\AutoPublish\ADSGroup\ExcludeList`.*Administrator.*\Exchange.*\.*Admins\.*Operators\IIS_IUSRS`1

Additional evidence (preprocessor relevance + effective evaluation):

SELECT FullPath, Enabled, IsEnabledResulting, Value, IsPreprocessorCondition, UID_ConfigParm, UID_ParentConfigparm
FROM DialogConfigParm
WHERE FullPath IN (
  'QER\ITShop',
  'QER\ITShop\AutoPublish',
  'QER\ITShop\AutoPublish\ADSGroup',
  'QER\ITShop\AutoPublish\ADSGroup\ExcludeList'
)
ORDER BY FullPath;

Observed (sandbox):

The parameter description (live DialogConfigParm.Description) explicitly states the compile requirement:

DB-side effective evaluation is done via a simple helper function:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.QBM_FGIConfigparmValue')) AS Def;

Observed (definition head, sandbox):

create function dbo.QBM_FGIConfigparmValue(@fullpath nvarchar(512)) returns nvarchar(1024) as
begin
  return(
    select top 1 x.wert from (
      select top 1 case when p.IsEnabledResulting = 1 and p.Value > N' ' then p.Value else N'' end as wert, 1 as Sortierung
      from DialogConfigParm p with (nolock) where p.FullPath = @fullpath
      union all select N'', 2
    ) as x
    order by x.Sortierung
  )
end

Interpretation (sandbox-specific): “Enabled” flags are materialized into IsEnabledResulting (incl. inheritance), and only then does Value become visible to code that reads config parameters.

Effective values as returned by the DB helper (live sandbox):

SELECT
 dbo.QBM_FGIConfigparmValue('QER\ITShop\AutoPublish') AS AutoPublish_Effective,
 dbo.QBM_FGIConfigparmValue('QER\ITShop\AutoPublish\ADSGroup') AS ADSGroup_Effective,
 dbo.QBM_FGIConfigparmValue('QER\ITShop\AutoPublish\ADSGroup\ExcludeList') AS ExcludeList_Effective,
 dbo.QBM_FGIConfigparmValue('QER\ITShop\AutoPublish\ADSGroup\AutoFillDisplayName') AS AutoFillDisplayName_Effective;

Observed:

3b) ITShopSrcHasADSGroup is compiled out via ITSHOPSOURCE (DialogTable preprocessor)

This sandbox contains the “IT Shop source” concept (ITShopSrc*) but it is deactivated via preprocessor condition ITSHOPSOURCE, which is why the corresponding view is compiled to where 1 = 0.

Query:

SELECT UID_DialogTable, TableName, PreProcessorCondition, IsDeactivatedByPreProcessor, TableType
FROM dbo.DialogTable
WHERE TableName IN ('ITShopSrcHasADSGroup','ITShopOrgHasADSGroup','BaseTreeHasADSGroup')
ORDER BY TableName;

Observed:

TableNamePreProcessorConditionIsDeactivatedByPreProcessorTableType
BaseTreeHasADSGroup*(empty)*0B
ITShopOrgHasADSGroupITSHOP0V
ITShopSrcHasADSGroupITSHOPSOURCE1V

This matches the live view definition:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.ITShopSrcHasADSGroup')) AS Def;

Observed (relevant line): ... from BaseTreeHasADSGroup where 1 = 0

4) The IT Shop placement table for AD groups exists, but none point into the IT Shop subtree

Query:

SELECT
  SUM(CASE WHEN b.UID_OrgRoot = 'QER-V-ITShopOrg' THEN 1 ELSE 0 END) AS inItShopRoot,
  COUNT(*) AS total
FROM dbo.BaseTreeHasADSGroup bha
JOIN dbo.BaseTree b ON b.UID_Org = bha.UID_Org;

Observed:

5) BaseTreeAssign links ADSGroup to its “BaseTreeHas<type>” table

Query:

SELECT
  dte.TableName AS elementTable,
  dtmn.TableName AS mnTable,
  bta.IsITShopEnabled,
  bta.IsEsetEnabled,
  bta.IsReusePossible,
  bta.IsReusePossibleUS
FROM dbo.BaseTreeAssign bta
LEFT JOIN dbo.DialogTable dte ON dte.UID_DialogTable = bta.UID_DialogTableElement
LEFT JOIN dbo.DialogTable dtmn ON dtmn.UID_DialogTable = bta.UID_DialogTableMN
WHERE dte.TableName IN ('ADSGroup','ESet','QERReuse','QERAssign','QERResource')
ORDER BY dte.TableName;

Observed (relevant lines):

6) Flag enforcement is implemented in triggers + QER helper proc/function

Triggers:

ADS_TUAdsGroup excerpt (from OBJECT_DEFINITION substring):

... if update(isForITShop) or update(isITShopOnly) begin
  declare @FlagTest QBM_YParameterlist
  insert into @FlagTest(...)
  select i.UID_AdsGroup, i.XObjectKey, i.IsForITShop, dbo.QBM_FCVBigIntToString(i.IsITShopOnly) from inserted i
  exec QER_PIsForITShopFlagCheck 'AdsGroup', @FlagTest, 'ADSAccountInADSGroup|ADSMachineInADSGroup'
end ...

QER_FGIITShopFlagCombineValid (definition head excerpt):