CODEWITHSUNDEEP
sqlsql-serverstored-procedures
sm2mafaz
sm2mafaz

Reputation: 402

Stored Procedure gets dropped randomly

We have recently encountered a strange problem on our staging and production environments. We have run an update script to a stored procedure on SQL Server 2005, verified the new change, and started using it on our products. After sometime the same stored procedure has gone missing from the DB. This stored procedure is not being used by any other tasks except the one we are intending to use. We have checked every bit of code and deployment script, but cannot find a trace for just dropping the stored procedure.

This issue doesn't occur on our DEV and QA environments, but on Staging and Production only.

Could anybody help on this?

Kind Regards,

Mafaz

Upvotes: 3

Views: 1407

Answers (1)

StuartLC
StuartLC

Reputation: 107237

If you've ruled out the obvious (e.g. deliberate sabotage), then I would suggest having a look through sys.sql_modules for a reference to the procedure - possibly there is an accidental slip like:

IF NOT EXISTS (SELECT 1 FROM SYS.PROCEDURES WHERE NAME = 'Proc1')
  DROP PROCEDURE Proc1
GO

CREATE PROC dbo.Proc1
  AS
    ...

<< MISSING GO!

IF NOT EXISTS (SELECT 1 FROM SYS.PROCEDURES WHERE NAME = 'Proc2')
  DROP PROCEDURE Proc2
GO

CREATE PROC dbo.Proc2
  AS
  ...

i.e. in the Above, the DROP PROCEDURE Proc2 code gets appended INTO the definition of the unrelated Proc1 because of a missing GO at the end of the Proc1 definition. Every time Proc1 is run, it will drop proc Proc2 (and the if exists will inconveniently hide an error if Proc2 is already dropped).

Similarly, another common issue is leaving GRANT EXEC at the bottom of the PROC - if permissions are lax, this can destroy the performance of a procedure.

The best advice here is to execute applications with minimal permissions, so that it is not able to execute DDL such as DROP or GRANT. This way, the app will break when Proc1 is executed, allowing you to quickly track down the offending code.

Upvotes: 6

Related Questions