SQL Server 2008 – How to Find the Stored Procedure Executing an Event

Getting the SQL from a DDL Trigger for whatever query that is dropping this Stored Procedure will only help so much. If the query is coming from Dynamic SQL from a Stored Procedure, or from a release script, or an integration test, application code, etc, then you will likely only capture the DROP PROCEDURE ... which doesn't give much of a clue as to where that is being executed from.

However, this does not mean that DDL Triggers are not the way to figure this out. Rather than merely capturing the SQL and trying to infer the source, since this action is unwanted (and likely breaks whatever code calls the Stored Procedure that is being deleted), it should simply be disallowed. You can capture the DROP PROCEDURE event using a DDL Trigger, and then check which procedure is being deleted via the XML returned from the EVENTDATA() function. If the Stored Procedure being deleted is the one in question, then execute something along the lines of:

RAISERROR('Ah ha! Caught you red-handed (whatever that means). No DROP for you!', 16, 1);
ROLLBACK;

Doing this:

• will prevent the stored procedure from being dropped (which is the desired outcome anyway).
• will identify the source(s) of the DROP query. There could be more than one source, especially if procedure calls are nested. This method will bubble the error up so that whoever, or whatever, is originating the call, will see an error message and likely alert you since their action is not completing successfully.
• will not prevent any other object from being dropped.

The following is a more complete example, including the ability to also log the event, just in case, since it can at least give some insight into which person or process is doing this, and how often:

CREATE TRIGGER [PreventDropGetPendingConfiguration]
ON DATABASE
FOR DROP_PROCEDURE
AS
  SET NOCOUNT ON;

  IF (EVENTDATA().value(N'(EVENT_INSTANCE/ObjectName/text())[1]', 'sysname')
        = N'usp_GetPendingConfiguration')
  BEGIN

    -- store values in variables as ROLLBACK will erase EVENTDATA()
    DECLARE @EventTime DATETIME,
            @LoginName sysname, -- lower-case for case-sensitive servers
            @UserName sysname, -- lower-case for case-sensitive servers
            @CommandText NVARCHAR(MAX),
            @SPID INT;

    DECLARE @InputBuffer TABLE
    (
      EventType NVARCHAR(30),
      [Parameters] SMALLINT,
      EventInfo NVARCHAR(4000)
    );

    SELECT @EventTime =
                 EVENTDATA().value(N'(EVENT_INSTANCE/PostTime/text())[1]', 'DATETIME'),
           @LoginName =
                 EVENTDATA().value(N'(EVENT_INSTANCE/LoginName/text())[1]', 'sysname'),
           @UserName =
                 EVENTDATA().value(N'(EVENT_INSTANCE/UserName/text())[1]', 'sysname'),
           @CommandText =
                EVENTDATA().value(N'(EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]',
                'NVARCHAR(MAX)'),
           @SPID = EVENTDATA().value(N'(EVENT_INSTANCE/SPID/text())[1]', 'INT');

    -- RollBack now else logging will also get Rolled Back ;-)
    ROLLBACK;

    IF (OBJECT_ID(N'dbo.LoggyLog') IS NULL)
    BEGIN
      CREATE TABLE dbo.LoggyLog
      (
        LoggyLogID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
        EventTime DATETIME NOT NULL,
        LoginName sysname, -- lower-case for case-sensitive servers
        UserName sysname, -- lower-case for case-sensitive servers
        CommandText NVARCHAR(MAX) NOT NULL,
        SPID INT NOT NULL,
        EventInfo NVARCHAR(4000) NULL
    );
    END;

    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'DBCC INPUTBUFFER ( ' + CONVERT(NVARCHAR(10), @SPID)
               + N' ) WITH NO_INFOMSGS;';

    INSERT INTO @InputBuffer (EventType, [Parameters], EventInfo)
      EXEC(@SQL);

    INSERT INTO dbo.LoggyLog (EventTime, LoginName, UserName, CommandText,
                              SPID, EventInfo)
      SELECT  @EventTime, @LoginName, @UserName, @CommandText, @SPID, tmp.EventInfo
      FROM    @InputBuffer tmp;

    RAISERROR('Ah ha! Caught you red-handed (whatever that means **). No DROP for you!',
              16, 1);
  END;

GO

Any attempt to drop this Stored Procedure will receive the following error:

Msg 50000, Level 16, State 1, Procedure PreventDropProcedure, Line 7
Ah ha! Caught you red-handed (whatever that means). No DROP for you! Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.

The reason I used DBCC INPUTBUFFER instead of sys.dm_exec_sql_text is that sys.dm_exec_sql_text returns the current query being executed. If sys.dm_exec_sql_text is queried natively within the Trigger itself, you end up with the CREATE TRIGGER... statement. If that DMV is queried in Dynamic SQL or a sub-stored procedure call, then you would get those particular queries and not even the CREATE TRIGGER that they were called from. All of that is useless.

In contrast, DBCC INPUTBUFFER reports the first batch (not just the current query) in the chain, and that can at least be used to trace any number of subsequent calls that lead to the DROP call.

Also, given that this only happens sometimes, it is possible that someone forgot a GO in a release script that was doing a CREATE PROCEDURE just before the DROP PROCEDURE was called, and accidentally made the DROP query part of the Stored Procedure that was being created (this happens more often with GRANT EXECUTE statements since they typically follow CREATE PROCEDURE statements). This can happen due to something like the following being in a release script:

CREATE PROCEDURE dbo.ProcName
AS
...

-- missing GO !!!!

IF (OBJECT_ID(N'dbo.ProcGettingDropped') IS NOT NULL)
BEGIN
  DROP PROCEDURE dbo.ProcGettingDropped;
END;
GO -- this GO terminates the CREATE PROCEDURE statement

You can search for occurrences of this in the database containing the Stored Procedure that is getting dropped by running the following query:

SELECT OBJECT_NAME([object_id]) AS [ObjectName], *
FROM   sys.sql_modules
WHERE  [definition] LIKE N'%DROP%';

** etymology of red handed (thanks to @MartinSmith)