Somewhere, probably between DNN versions 4.9.5 and 5.4.2, tab management became a bit buggy and some properties were not updated correctly when tabs were moved around. This script finds, and optionally fixes, incorrect Level and TabPath values.

There are a few scripts on the DNN forums that update the Level based on the TabPath value - this approach is fine providing the TabPath values are correct but I've seen cases where this has not been the case. This script works using only TabId (which is right by definition) and ParentId (which I've not seen to be wrong).

Running the script as-is will display tabs with incorrect Level and/or TabPath values. To fix any problems comment out the final SELECT and uncomment the UPDATE statement. After using the script to fix problems perform an app restart (Host Settings tab) to clear cached data and prevent the scheduler throwing 'System.UnauthorizedAccessException' exceptions. The script as provided ignores Admin and Host tabs. Some Admin and Host tabs use a non standard TabPath format which is not supported by this script. You may need to update table references in the script if your DNN database uses non-default {databaseOwner} and {objectQualifier} values.

The script requires SQL Server 2005 or later as it uses a recursive CTE to walk the tab hierarchy.

Originally written 17th July 2010. Scripts provided as-is without warranty, use at your own risk.

if (object_id(N'dbo._GetTabPathName') is not null)
   drop function dbo._GetTabPathName
go

create function dbo._GetTabPathName(@TabName nvarchar(50)) returns nvarchar(50) as
begin
   declare @r nvarchar(50); set @r = N''
   declare @c nchar(1)
   declare @i int; set @i = 1
   while (@i <= len(@TabName))
   begin
      set @c = substring(@TabName, @i, 1)
      -- This test assumes a case-insensitive collation.
      if ((@c >= N'A' and @c <= N'Z') or (@c >= N'0' and @c <= N'9') or @c = N'_') set @r = @r + @c
      set @i = @i + 1
   end
   return @r
end
go

; with cte as (
   select 
      t1.TabID,
      t1.ParentId,
      t1.PortalID,
      t1.TabName,
      t1.Level,
      0 as ActualLevel,
      null as ParentLevel,
      t1.TabPath,
      cast(N'//' + dbo._GetTabPathName(t1.TabName) as nvarchar(255)) as ActualTabPath
   from
      dbo.Tabs t1
   where 
      t1.ParentId is null and
      t1.IsDeleted = 0
union all
   select 
      t2.TabID,
      t2.ParentId,
      t2.PortalID,
      t2.TabName,
      t2.Level,
      t1.ActualLevel + 1 as ActualLevel,
      t1.Level as ParentLevel,
      t2.TabPath,
      cast(t1.ActualTabPath + N'//' + dbo._GetTabPathName(t2.TabName) as nvarchar(255)) as ActualTabPath
   from
      cte t1
         join dbo.Tabs t2 on t2.ParentId = t1.TabID
   where
      t2.IsDeleted = 0
)

-- SELECT statement - displays problems only
select 
   * 
from 
   cte
where
   Level <> ActualLevel or
   (TabPath <> ActualTabPath and
   left(TabPath,8) <> N'//Host//' and
   left(TabPath,9) <> N'//Admin//')

---- UPDATE statement - changes
--update t set
--   Level = c.ActualLevel,
--   TabPath = c.ActualTabPath,
--   LastModifiedOnDate = getdate()
--from
--   dbo.Tabs t
--      inner join cte c on c.TabID = t.TabID
--where
--   t.Level <> c.ActualLevel or
--   (t.TabPath <> c.ActualTabPath and
--   left(t.TabPath, 8) <> N'//Host//' and
--   left(t.TabPath, 9) <> N'//Admin//')
go

if (object_id('dbo._GetTabPathName') is not null)
   drop function dbo._GetTabPathName
go