0

I have a sp that has been working for 2 years and this weekend it has stopped

error is

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

1 Answer 1

0

Also I'm a member of sysadmin

If your login is a sysadmin, then when you connect to a database you are dbo, not a mapped user.

So in this case you should just “execute as owner” if both the table and procedure are owned by dbo.

6
  • Would the sudden change in behavior be explained by dropping a database user for OP's login (or an AD group containing it), or even a change in membership on the AD side?
    – AMtwo
    Jul 11, 2021 at 13:10
  • It's hard to speculate, but you can't create a proc with execute as a non-existent user, or drop a user that a proc executes as. But the user could have lost permission some other way. Jul 11, 2021 at 13:52
  • 1
    checked and AD Group hasn't changed so not sure why its no longer working but I can confirm run as Owner does work so using that. Thanks @DavidBrowne-Microsoft
    – Michael
    Jul 11, 2021 at 23:51
  • I found the cause of the issue today. The owner of the db I'm running the db from was not a sysadmin, making the owner a sysadmin fixed it :) A script from external vendor change owner
    – Michael
    Jul 14, 2021 at 0:44
  • You fixed your SP problem but weakened your security, having SA as DB owner is not best practice. insidesql.org/blogs/andreaswolter/2014/06/… Jul 15, 2021 at 13:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.