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