Maintain Database Indexes job fails if custom schemas used

Found in

EPiServer.Commerce 12.16.0

Fixed in

EPiServer.Commerce 13.3.1

Created

May 09, 2019

Updated

Jun 07, 2019

State

Closed, Fixed and tested


Description

Steps to reproduce:
1. Create a table and indexes with a different schema like this example:

USE [XXXDatabasenameXXX]
GO
/****** Object:  Schema [storefront]    Script Date: 2019-05-09 11:09:50 ******/
CREATE SCHEMA [storefront]
GO 
 
/****** Object:  Table [storefront].[ModuleSet]    Script Date: 2019-05-09 11:08:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [storefront].[ModuleSet](
                [Id] [int] IDENTITY(1,1) NOT NULL,
                [Group] [nvarchar](255) NOT NULL,
                [Ready] [bit] NOT NULL,
CONSTRAINT [PK_Storefront_ModuleSet_Id] PRIMARY KEY CLUSTERED
(
                [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [storefront].[StagingDocuments](
                [Id] [int] IDENTITY(1,1) NOT NULL,
                [Key] [nvarchar](255) NOT NULL,
                [Data] [nvarchar](max) NOT NULL,
                [Type] [int] NOT NULL,
                [StoreId] [nvarchar](255) NOT NULL,
                [Group] [nvarchar](255) NULL,
                [Set] [int] NOT NULL,
                [CreatedUtc] [datetime2](7) NOT NULL,
CONSTRAINT [PK_Storefront_StagingDocuments_Id] PRIMARY KEY CLUSTERED
(
                [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Index [IDX_Storefront_StagingDocuments_Type_StoreId]    Script Date: 2019-05-09 11:17:47 ******/
CREATE NONCLUSTERED INDEX [IDX_Storefront_StagingDocuments_Type_StoreId] ON [storefront].[StagingDocuments]
(
                [Type] ASC,
                [StoreId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

2. Add <add key="episerver::LowFragmentationThreshold" value ="-1"/> to your appSettings.
3. Run the "Maintain Database Indexes" scheduled job.

Expected:
Should rebuild indexes

Actual:
Get error when running job. "Cannot find the object" ModuleSet "because it does not exist or you do not have permissions."