Who Update the Stored procedure Last time?

  • Hello EveryOne,

    Need your help once again.

    I have lots of Stored PRocedures in my Database and we are 3 sql dba/developer .

    But need to know who updated the stored procedure last time?

    or by whom the stored procedure was updated last time??

    Any Help or suggestion would be appriciated.

    Thanks in advance.

  • We store all Sprocs in individual files (with a folder structure for Project / Client / whatever)

    If I want to modify an Sproc I just open the file, work on it, execute it, and save it (to my C: local drive)

    Then I CHECKIN to (in our case) SVN.

    SVN stores every previous version of the file, date, time, and UserID

    If someone else has also edited THAT file today then either my CHECKIN will fail (and provide me with both my version and the repository version so I can compare) or it will merge the changes (if they don't conflict)

    We can use that to review who changed what, do Peer Review of code, "export" all changes "Since the last rollout" to make a single rollout script and so on.

    At the top of every script file we have a call to a "ScriptLog" SProc - (Paameters for Object name and Version No) that just log that the script was run, the currently connected UserID, IP address etc.

    That tells us who EXEC'd which script files. Also we can compare EXEC date/version between e.g. DEV and PPRODUCTION to see "what is newer on DEV" or (hope you never do this!) what is newer on PRODUCTION !!

    If you are just using EDIT in SSMS to change an SPROC I recommend you stop doing that RIGHT NOW!

  • There is a schema change report - it might contain that information if the trace data is still available.  I would check there first - right-click on the database in SSMS (Object Explorer), select Reports | Standard Reports | Schema Changes History.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply