DB Load throws "The default schema does not exist" on upgrade

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

During an upgrade of the initialdata on SQL Server, the database was restored from a previous backup and the following error appears in the db load utility logs:

20221115/225945.267 - U00003592 UCUDB - Status: '42000' Native error: '2797' Msg: 'The default schema does not exist.'

Environment

Release : All

Resolution

The login did not exist in SQL Server.  This also meant that the user did not have a default database assigned to it. 

This can happen when the database is imported and the owner doesn't change.

The following sql statement helped in discovering this:

select sp.name as login,
       sp.type_desc as login_type,
       sl.password_hash,
       sp.create_date,
       sp.modify_date,
       case when sp.is_disabled = 1 then 'Disabled'
            else 'Enabled' end as status, sp.name, sp.default_database_name
from sys.server_principals sp
left join sys.sql_logins sl
          on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R') and sp.name='[user from sqldriverconnect settings]'
order by sp.name;

A DBA should put the user in place and assign them a default database/schema.