I want to find out the following details  in sql server 2008

1) who created this sp
2) who has last modified this sp

Please let me know how do i find this out.
I am able to find the created and modified dates only using sys.objects but i need the user names also.

Thanks in advance.
Raj

SQL Server doesn't store this information in its internal system tables. You can see who *owns* the procedure, but that is not neccesarily the same as who created or last modified the procedure. You might be able to find out through the defult trace (the your data folder for .trc files), but this informaiton is only kept for 5 files, 100MB each.

To add to Tibor's reply, there is a Standard Report that will show you the schema change information that comes from the default trace. This simplifies things somewhat for you.  (Unless you have the default trace turned off.)

In SSMS, right click on Server Name, choose Reports / Standard Reports / Schema Changes History.

The list is in time order with most recent at the top.  However, once the report is created, you can export to Excel to make it easier to search for a specific name.



RLF

• Marked as answer by Alex Feng (SQL)Moderator Thursday, February 04, 2010 2:40 AM

DECLARE @FILENAME VARCHAR(255) 
SELECT @FILENAME = SUBSTRING(PATH, 0, LEN(PATH)-CHARINDEX('\', REVERSE(PATH))+1) + '\LOG.TRC'  
FROM SYS.TRACES   
WHERE IS_DEFAULT = 1;  

--SELECT @FILENAME

SELECT GT.HOSTNAME, 
       GT.APPLICATIONNAME, 
       GT.NTUSERNAME, 
       GT.NTDOMAINNAME, 
       GT.LOGINNAME, 
       GT.SPID, 
       GT.EVENTCLASS, 
       TE.NAME AS EVENTNAME,
       GT.EVENTSUBCLASS,      
       GT.TEXTDATA, 
       GT.STARTTIME, 
       GT.ENDTIME, 
       GT.OBJECTNAME, 
       GT.DATABASENAME, 
       GT.FILENAME, 
       GT.ISSYSTEM
FROM [FN_TRACE_GETTABLE](@FILENAME, DEFAULT) GT 
INNER JOIN SYS.TRACE_EVENTS TE ON GT.EVENTCLASS = TE.TRACE_EVENT_ID 
WHERE EVENTCLASS IN (164) 
--AND GT.EVENTSUBCLASS = 2
--AND GT.NTUSERNAME LIKE 'GO%'
--AND GT.OBJECTNAME = 'SPR_SHIFT_CASH_RECON_TOT_MVP_NEW_CHANGES'
ORDER BY STARTTIME DESC; 

 OR

GO
SELECT NAME, CREATE_DATE, MODIFY_DATE, *
FROM SYS.OBJECTS
WHERE TYPE = 'P'
AND NAME = 'SPR_SHIFT_CASH_RECON_TOT_MVP_NEW_CHANGES'

You will have to create a DDL trigger on the database level for such purpose.

CREATE TRIGGER [trgLogDDLEvent] ON DATABASE 
    FOR DDL_DATABASE_LEVEL_EVENTS 
AS 
    DECLARE @data XML 
    SET @data = EVENTDATA() 
    IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 
        <> 'CREATE_STATISTICS' AND  @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
        <>  'UPDATE_STATISTICS' 
        BEGIN 
        INSERT  INTO DDLChangeLog 
                ( 
                  EventType, 
                  ObjectName, 
                  ObjectType, 
                  tsql ,
                  Session_IPAddress
                ) 
                SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 
                              'nvarchar(100)'), 
                  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 
                              'nvarchar(max)'), client_net_address
                 FROM sys.dm_exec_connections WHERE session_id=@@SPID
          END
; 



GO

client_net_address is a IP address of the person who did changes.