Cannot drop table from within SQL Job, BUT same user using SSMS does not get error

Question

Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64)
Jun 22 2022 18:20:15
Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

We have a SQL Job that has been working without issue until CU30.

We have following command in the script:

if exists(select name from sysobjects where name='table1')  
begin  
 drop table [dbo].[table1]  
end  

The SQL History Log shows the following error:
Executed as user: domain\user1. Cannot drop the table 'table1', because it does not exist or you do not have permission.

The owner of the job is sa and it seems to be running under my user which is fine. I have confirmed Server Roles as public and sysadmin.
The user also has user mapping to the database as db_owner, public and another role required for a specific application.

Nothing has changed here, but after that last CU30 this started happening and makes no sense.

Logged in as that user on SSMS the exact same commands from that SQL Job step it runs without issue.

Is this some sort of bug introduced in CU30?

Thanks in advance for your feedback!

Answer 1

Your query returns all objects. "table1" may not be a table or in the dbo schema.

The simplest way to do this is to run:

drop table if exists [dbo].[table1];  

Answer 2

I agree with Tom. There is all reason to modernise this. sysobjects have been deprecated since 2005.

At the time the query runs, Table1 may not be a table. Or there may be an object in a different schema with the same name,

Change it to

DROP TABLE IF EXISTS dbo.table1

and be done with it.