Home Forums SQL Server 2005 Administering How to findout who has modified the Stored procedures recently RE: How to findout who has modified the Stored procedures recently

  • This doesn't get you IP Address, but it will get you the rest:

    SELECT

    I.*

    FROM

    sys.traces T CROSS Apply

    ::fn_trace_gettable(T.path, T.max_files) I Join

    sys.trace_events E On

    I.eventclass = E.trace_event_id

    Where

    T.id = 1 And

    E.name = 'Object:Altered'

    This is based on the Default trace that is installed and started when SQL Server starts. It created a maximum of 5 20 MB trace files and rolls them over so when the 5th is full it deletes the oldest and creates a new file. It also creates a new file whenever the SQL Server is restarted. If you need to keep more files you can create a process that archives them or puts the data in a table.

    I don't know of a way to get the IP Address.

    Another option is create a DDL trigger that logs information.