Turned out, the HP application (HP Application Lifecycle Management, otherwise known as ALM or previously known as Quality Center) was running a process to upgrade from a previous version where the DB was moved from SQL 2005 to SQL 2008.
The tables in the source system are owned by a user 'td' and in the target system, they need to be owned by user 'dbo'. I was aware of this and had run ALTER TABLE to change owners but a few tables were missed (my bad).
The message "Some tables were created with SQL Server Authentication and some with Windows Authentication" is a message from the HP software and it means some tables are still owned by 'td' rather than 'dbo'.
An ALTER TABLE to change the owner to 'dbo' for the tables that remained owned by 'td' resolved the issue.