none
which user modified the stored procedure

    Question

  • 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
    Wednesday, January 27, 2010 12:51 PM

Answers

  • 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
    Wednesday, January 27, 2010 4:36 PM
  • 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.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, January 27, 2010 12:56 PM

All replies

  • 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.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, January 27, 2010 12:56 PM
  • 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
    Wednesday, January 27, 2010 4:36 PM

  • 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,
           O.TYPE_DESC
    FROM [FN_TRACE_GETTABLE](@FILENAME, DEFAULT) GT 
    INNER JOIN SYS.TRACE_EVENTS TE ON GT.EVENTCLASS = TE.TRACE_EVENT_ID
    INNER JOIN SYS.OBJECTS O ON GT.OBJECTID=O.OBJECT_ID
    WHERE EVENTCLASS NOT IN (175,116,79,69,55,46)
    AND EVENTCLASS IN (164) --Object:Altered 
    AND O.TYPE IN ('P','FN') --'P' for Stored Procedure, 'FN' for Function
    --AND GT.EVENTSUBCLASS IN (0,1)
    --AND GT.NTUSERNAME LIKE 'GO%'
    --AND GT.OBJECTNAME = 'SPR_Test'
    ORDER BY STARTTIME DESC; 

     OR

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

    Thanks Shiven:) If Answer is Helpful, Please Vote


    Monday, January 20, 2014 5:46 AM
  • 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.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 20, 2014 6:22 AM