Determining how a schema change occurred?

Something bad happened yesterday.

A view that was created sometime back ago was modified by someone which eventually broke the reports. Unfortunately. somebody (knowingly or unknowingly) did this modification in PRODUCTION database.

My Question: Is there a way (script/software/freeware etc) by which we can come to know who (username) did this modification, so that i can revoke the access to production database for that user.

If my question is unclear, please comment.

This gets logged to the default trace so, as long as it is enabled and hasn't rolled over in the meantime it should appear in the "Schema Changes History" report.

To access this in Management Studio right click the database then from the context menu choose Reports -> Standard Reports -> Schema Changes History

To retrieve the same information via TSQL you can use

SELECT StartTime
       ,LoginName
       --,f.*
FROM   sys.traces t
       CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),
                                                       CHARINDEX('\', REVERSE(t.path)), 
                                                       260)
                                             ) + N'log.trc', DEFAULT) f
WHERE  t.is_default = 1
       AND ObjectName = 'FOO'
       AND EventClass IN (46, /*Object:Created*/
                          47, /*Object:Dropped*/
                          164 /*Object:Altered*/ )

StackExchange Database Administrators Q#10716, answer score: 37