CODEWITHSUNDEEP
c#asp.netsql-serverstored-procedures
Mucida
Mucida

Reputation: 603

SQL Server stored procedure being deleted

I have a stored procedure that is being delete while clients are using the system, and they don't have access to the database. So they could not delete it manually

I saw a lot of people saying that it could be a missing GO before IF EXISTS... DROP PROC statement, but this procedure is not disappearing at the moment we run the script to update the database, it is being deleted while the users are using the system.

There is no DROP at the C# code. So I don't have idea what is going on. What could be dropping it?

Upvotes: 0

Views: 939

Answers (2)

Solomon Rutzky
Solomon Rutzky

Reputation: 48786

I think the people saying that a "GO" was missing meant that the IF EXISTS DROP is now embedded inside some stored procedure. This would explain why it disappears when people are using the system and not when you run the update script (since the IF EXISTS DROP is part of a proc definition and not immediately runnable). Run the following:

SELECT *
FROM sys.sql_modules
WHERE [definition] LIKE N'%DROP_%PROC%';
-- Use _% between the words to account for whitespace variations but still require
-- at least one character

in that database to see if anything shows up.

To explain what has happened, your script looks like the following:

// a bunch of SQL statements

GO
IF EXISTS(...SomeProc1)
   DROP PROC dbo.SomeProc1;
GO
CREATE PROC dbo.SomeProc1
AS
 ....

// oops, no GO here

IF EXISTS(...SomeProc2)
   DROP PROC dbo.SomeProc2;
GO
CREATE PROC dbo.SomeProc2
AS
 ....

GO

Because there is no "GO" between the CREATE PROC dbo.SomeProc1 and the IF EXISTS(...SomeProc2), the IF EXISTS(...SomeProc2) is now part of SomeProc1 (at the end of it). Each time SomeProc1 runs, it will drop SomeProc2.

You can also check the script that did the database updates, look for the definition of the proc that is being dropped, and look just above it to see what code it became a part of.

You should also check your SQL Agent jobs to make sure that there isn't a step that has an embedded DROP:

SELECT *
FROM msdb.dbo.sysjobsteps
WHERE [command] LIKE N'%DROP_%PROC%';

Upvotes: 5

HLGEM
HLGEM

Reputation: 96552

I would suggest putting a DDL trigger for DROP_PROCEDURE

Then you can prevent the drop unless the system admin does it. Or you can record when it was dropped and by who so you can research what is happening.

Upvotes: 0

Related Questions