Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

marked as duplicate by Martin Smith, Aaron Bertrand, RolandoMySQLDBA, Mark Storey-Smith, Marian Oct 10 '12 at 12:42

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
Yes this is very similar to that question, I didn't see that one, my problem unfortunately continues as you can see in my original question I have already tried the most highly voted answer of that question. –  User Smith Sep 27 '12 at 21:06
    
Yes. I think that the SSMS reports look at the default trace so if they don't show it the info is probably gone (though without checking the code I'm not sure if the SSMS reports look at the historic rolled over files in the default trace directory) –  Martin Smith Sep 27 '12 at 21:09
    
Thanks Martin, I am not very versed in this arena, I had read something of that sort but wasn't sure if it was true. –  User Smith Sep 27 '12 at 22:02

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
1  
Definitely a plus one for solving the problem in the future many thanks Max. –  User Smith Sep 27 '12 at 21:04
1  
@Max Do you happen to have this on a blog post or something so I might be able to reference it properly, not that I can't just reference this question :) –  Shawn Melton Sep 27 '12 at 23:26
    
Thanks, Shaun - I'll put it on my blog and get back to you –  Max Vernon Sep 27 '12 at 23:57
1  
@Shaun - here is a preliminary blog post - I'll add to it tomorrow! blog.mvct.com/technology/… –  Max Vernon Sep 28 '12 at 0:13

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