Who changed/modified stored procedure in SSMS 2005 or 2008? [duplicate]

Question

Possible Duplicate:
Determining how a schema change occurred?

I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to this change.

Basically no one is owning up to this change, so I am trying to figure out how to find who modified this stored procedure. I have researched out on the web to see how to find this but it looks like this is not possible?

I figured if anyone knows how to do this it would be someone here.

I did try this. :

"In SSMS, right click on Server Name, choose Reports / Standard Reports / Schema Changes History." --> Which didn't give me anything but I was at least able to run this which provided me with a time.

select name, create_date, modify_date
from sys.procedures
where name = 'mystoredprocedure'

Thanks for any advice or direction.

Answer 1

I know this doesn't solve your immediate problem, however the best way to pro-actively manage this type of event moving forward may be to create a DDL Database Trigger that logs events into a design log table such as:

CREATE TABLE dbo.DesignLog(
    DesignLogID int NOT NULL PRIMARY KEY CONSTRAINT PK_DesignLog IDENTITY(1,1),
    DateStamp datetime NULL CONSTRAINT DF_DesignLog_DateStamp  DEFAULT (getdate()),
    HostName nvarchar(255) NULL CONSTRAINT DF_DesignLog_HostName  DEFAULT (host_name()),
    UserName nvarchar(255) NULL CONSTRAINT DF_DesignLog_UserName  DEFAULT (suser_sname()),
    ActionType nvarchar(255) NULL,
    StatementExecuted nvarchar(max) NULL
);
GO
CREATE TRIGGER [DesignLogTrigger] 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
    /*
        Authored By:                    Max Vernon
        Author Date:                    2012-07-05
        Purpose:                        Capture DDL Statements executed against database
    */
    SET NOCOUNT ON;

    DECLARE @data XML;
    DECLARE @ActionType nvarchar(max);
    DECLARE @Statement nvarchar(max);
    DECLARE @msg nvarchar(max);
    SET @data = EVENTDATA();
    SET @msg = cast(@data.query('(/EVENT_INSTANCE/TSQLCommand/CommandText)') as nvarchar(max));
    SET @msg = dbo.xmltostring(@data.query('(/EVENT_INSTANCE/TSQLCommand/CommandText)'));
    SET @msg = dbo.RemoveTag(@msg, '');
    SET @msg = dbo.RemoveTag(@msg, '');
    SET @ActionType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)');
    SET @Statement = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'); 
    IF @msg NOT LIKE 'UPDATE STATISTICS' + char(37) + '' 
        AND @msg NOT LIKE 'ALTER INDEX' + char(37) + 'REORGANIZE' + char(37) + '' 
        AND @msg NOT LIKE 'ALTER INDEX' + char(37) + 'REBUILD' + char(37) + ''
    BEGIN
        BEGIN TRAN;
        INSERT INTO DesignLog 
            (
             ActionType
            , StatementExecuted
            ) 
        VALUES 
            (
             @ActionType
            , @Statement 
            );
        COMMIT TRAN;
    END
END;
GO
ENABLE TRIGGER [DesignLogTrigger] ON DATABASE;

This particular DDL Trigger does not log certain events such as UPDATE STATISTICS, INDEX REBUILD, AND INDEX REORGANIZE. We are not interested in logging these events since these events happen during our nightly database maintenance tasks, and therefore crowd out the meaningful changes made by developers.

The advantage of this particular trigger is that it captures the actual SQL text of the event, thereby creating a log of changes made to all objects in the database. This allows you to revert back to a prior version of any given object - a sort of manual revision control system.