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!