FYI to all - I just did a DNN 4.5.4 upgrade, and there appears to be an error in the DNN4.5.4 upgrade script, which will affect those of us with non-standard database owner or object qualifier settings. One of the symptoms is:
A critical error has occurred.
Invalid object name 'Lists'. Invalid object name 'Lists'.
when attempting to access module settings.
The solution is below. In the file \Providers\DataProviders\SqlDataProvider\04.05.04.SqlDataProvider, the first stored procedure, GetRolesByGroup, is incorrect. It will cause the error above and a variety of similar errors when retrieving module settings, accessing lists and a variety of other places where security roles and checks are done. The problem is a missing {databaseOwner}{objectQualifier} tag set before referencing 'Lists' in the query join in this procedure. The corrected file is below - I've highlighted the missing section:
CUT FROM HERE TO END AND REPLACE IN THE FILE ABOVE:
/************************************************************/
/***** SqlDataProvider *****/
/***** *****/
/***** *****/
/***** Note: To manually execute this script you must *****/
/***** perform a search and replace operation *****/
/***** for {databaseOwner} and {objectQualifier} *****/
/***** *****/
/************************************************************/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}GetRolesByGroup]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
drop procedure {databaseOwner}{objectQualifier}GetRolesByGroup
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetRolesByGroup]
@RoleGroupId int,
@PortalId int
AS
SELECT R.RoleId,
R.PortalId,
R.RoleGroupId,
R.RoleName,
R.Description,
'ServiceFee' = case when convert(int,R.ServiceFee) <> 0 then R.ServiceFee else null end,
'BillingPeriod' = case when convert(int,R.ServiceFee) <> 0 then R.BillingPeriod else null end,
'BillingFrequency' = case when convert(int,R.ServiceFee) <> 0 then L1.Text else '' end,
'TrialFee' = case when R.TrialFrequency <> 'N' then R.TrialFee else null end,
'TrialPeriod' = case when R.TrialFrequency <> 'N' then R.TrialPeriod else null end,
'TrialFrequency' = case when R.TrialFrequency <> 'N' then L2.Text else '' end,
'IsPublic' = case when R.IsPublic = 1 then 'True' else 'False' end,
'AutoAssignment' = case when R.AutoAssignment = 1 then 'True' else 'False' end,
R.RSVPCode,
R.IconFile
FROM {databaseOwner}{objectQualifier}Roles R
LEFT OUTER JOIN {databaseOwner}{objectQualifier}Lists L1 on R.BillingFrequency = L1.Value and L1.ListName = 'Frequency'
LEFT OUTER JOIN {databaseOwner}{objectQualifier}Lists L2 on R.TrialFrequency = L2.Value and L2.ListName = 'Frequency'
WHERE (RoleGroupId = @RoleGroupId OR (RoleGroupId IS NULL AND @RoleGroupId IS NULL))
AND R.PortalId = @PortalId
ORDER BY R.RoleName
GO
ALTER TABLE {databaseOwner}{objectQualifier}Tabs ADD
IsSecure bit NOT NULL CONSTRAINT DF_{objectQualifier}Tabs_IsSecure DEFAULT (0)
GO
DROP VIEW {databaseOwner}{objectQualifier}vw_Tabs
GO
CREATE VIEW {databaseOwner}{objectQualifier}vw_Tabs
AS
SELECT
T.TabID,
T.TabOrder,
T.PortalID,
T.TabName,
T.IsVisible,
T.ParentId,
T.[Level],
CASE WHEN LEFT(LOWER(T.IconFile), 6) = 'fileid'
THEN
(SELECT Folder + FileName
FROM {databaseOwner}{objectQualifier}Files
WHERE 'fileid=' + convert(varchar,{databaseOwner}{objectQualifier}Files.FileID) = T.IconFile
)
ELSE
T.IconFile
END
AS IconFile,
T.DisableLink,
T.Title,
T.Description,
T.KeyWords,
T.IsDeleted,
T.SkinSrc,
T.ContainerSrc,
T.TabPath,
T.StartDate,
T.EndDate,
T.URL,
CASE WHEN EXISTS (SELECT 1 FROM {databaseOwner}{objectQualifier}Tabs T2 WHERE T2.ParentId = T .TabId) THEN 'true' ELSE 'false' END AS 'HasChildren',
T.RefreshInterval,
T.PageHeadText,
T.IsSecure
FROM {databaseOwner}{objectQualifier}Tabs AS T
GO
drop procedure {databaseOwner}{objectQualifier}AddTab
GO
CREATE procedure {databaseOwner}{objectQualifier}AddTab
@PortalId int,
@TabName nvarchar(50),
@IsVisible bit,
@DisableLink bit,
@ParentId int,
@IconFile nvarchar(100),
@Title nvarchar(200),
@Description nvarchar(500),
@KeyWords nvarchar(500),
@Url nvarchar(255),
@SkinSrc nvarchar(200),
@ContainerSrc nvarchar(200),
@TabPath nvarchar(255),
@StartDate datetime,
@EndDate datetime,
@RefreshInterval int,
@PageHeadText nvarchar(500),
@IsSecure bit
as
insert into {databaseOwner}{objectQualifier}Tabs (
PortalId,
TabName,
IsVisible,
DisableLink,
ParentId,
IconFile,
Title,
Description,
KeyWords,
IsDeleted,
Url,
SkinSrc,
ContainerSrc,
TabPath,
StartDate,
EndDate,
RefreshInterval,
PageHeadText,
IsSecure
)
values (
@PortalId,
@TabName,
@IsVisible,
@DisableLink,
@ParentId,
@IconFile,
@Title,
@Description,
@KeyWords,
0,
@Url,
@SkinSrc,
@ContainerSrc,
@TabPath,
@StartDate,
@EndDate,
@RefreshInterval,
@PageHeadText,
@IsSecure
)
select SCOPE_IDENTITY()
GO
drop procedure {databaseOwner}{objectQualifier}UpdateTab
GO
CREATE procedure {databaseOwner}{objectQualifier}UpdateTab
@TabId int,
@TabName nvarchar(50),
@IsVisible bit,
@DisableLink bit,
@ParentId int,
@IconFile nvarchar(100),
@Title nvarchar(200),
@Description nvarchar(500),
@KeyWords nvarchar(500),
@IsDeleted bit,
@Url nvarchar(255),
@SkinSrc nvarchar(200),
@ContainerSrc nvarchar(200),
@TabPath nvarchar(255),
@StartDate datetime,
@EndDate datetime,
@RefreshInterval int,
@PageHeadText nvarchar(500),
@IsSecure bit
as
update {databaseOwner}{objectQualifier}Tabs
set TabName = @TabName,
IsVisible = @IsVisible,
DisableLink = @DisableLink,
ParentId = @ParentId,
IconFile = @IconFile,
Title = @Title,
Description = @Description,
KeyWords = @KeyWords,
IsDeleted = @IsDeleted,
Url = @Url,
SkinSrc = @SkinSrc,
ContainerSrc = @ContainerSrc,
TabPath = @TabPath,
StartDate = @StartDate,
EndDate = @EndDate,
RefreshInterval = @RefreshInterval,
PageHeadText = @PageHeadText,
IsSecure = @IsSecure
where TabId = @TabId
GO
/************************************************************/
/***** SqlDataProvider *****/
/************************************************************/