I have a sp that has been working for 2 years and this weekend it has stopped
Msg 50000, Level 14, State 20, Procedure (cspSPName) Line 770 (Batch
Start Line 11) Cannot drop the table ‘TimeTempC’, because it does not
exist or you do not have permission.
This is an extract from the sp
ALTER PROC (dbo).(cspSPName) @Date bDate ='2021-03-29' WITH EXEC AS 'my user name is here' --other non relevant code in between IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'(dbo).(TimeTempC)') AND type in (N'U')) DROP TABLE (dbo).(TimeTempC)
If i remove the line
WITH EXEC AS 'my user name is here'
it works, with it , it doesnt
The user that I’m executing the code as is me. And if i take the line of code its failing on and run it in SQL Management Studio it runs fine.
FYI running the following version of SQL
Microsoft SQL Server 2017 (RTM-CU24) (KB5001228) – 14.0.3391.2 (X64) Apr 28 2021 10:32:18 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)
Also I’m a member of sysadmin