Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There has been a change in our DB and one of the SPs has been deleted. We have no idea who did it or when.

Is there any way to find it out? Or enabling an option to be able to track this in future?

share|improve this question
    
The "when" could be resolved by checking all backups (an idea from a no-DBA) –  Fabian Blechschmidt Apr 21 at 18:08
add comment

3 Answers 3

up vote 6 down vote accepted

There are couple of ways to do such forensic analysis :

  1. Using Default trace (provided that the default trace is running). The default trace create 5 *.TRC file that are recycled when they arrive to 20 MB size. :

    -- create a test procedure .. so that we can drop it
    use test_kin
    go
    create procedure usp_ABOUT_TO_DELETE 
    as
    select name from sysobjects
    where type = 'U'
    order by name 
    
    -- now drop the procedure
    drop procedure usp_ABOUT_TO_DELETE
    

    enter image description here

    Below is the sql to get details out of the default trace :

             /*
              Object Altered
              Object Created
              Object Deleted 
              */
    
    SELECT TE.NAME
        ,v.subclass_name
        ,DB_NAME(t.DatabaseId) AS DBName
        ,T.NTDomainName
        ,t.NTUserName
        ,t.HostName
        ,t.ApplicationName
        ,t.LoginName
        ,t.Duration
        ,t.StartTime
        ,t.ObjectName
        ,CASE t.ObjectType
            WHEN 8259
                THEN 'Check Constraint'
            WHEN 8260
                THEN 'Default (constraint or standalone)'
            WHEN 8262
                THEN 'Foreign-key Constraint'
            WHEN 8272
                THEN 'Stored Procedure'
            WHEN 8274
                THEN 'Rule'
            WHEN 8275
                THEN 'System Table'
            WHEN 8276
                THEN 'Trigger on Server'
            WHEN 8277
                THEN '(User-defined) Table'
            WHEN 8278
                THEN 'View'
            WHEN 8280
                THEN 'Extended Stored Procedure'
            WHEN 16724
                THEN 'CLR Trigger'
            WHEN 16964
                THEN 'Database'
            WHEN 16975
                THEN 'Object'
            WHEN 17222
                THEN 'FullText Catalog'
            WHEN 17232
                THEN 'CLR Stored Procedure'
            WHEN 17235
                THEN 'Schema'
            WHEN 17475
                THEN 'Credential'
            WHEN 17491
                THEN 'DDL Event'
            WHEN 17741
                THEN 'Management Event'
            WHEN 17747
                THEN 'Security Event'
            WHEN 17749
                THEN 'User Event'
            WHEN 17985
                THEN 'CLR Aggregate Function'
            WHEN 17993
                THEN 'Inline Table-valued SQL Function'
            WHEN 18000
                THEN 'Partition Function'
            WHEN 18002
                THEN 'Replication Filter Procedure'
            WHEN 18004
                THEN 'Table-valued SQL Function'
            WHEN 18259
                THEN 'Server Role'
            WHEN 18263
                THEN 'Microsoft Windows Group'
            WHEN 19265
                THEN 'Asymmetric Key'
            WHEN 19277
                THEN 'Master Key'
            WHEN 19280
                THEN 'Primary Key'
            WHEN 19283
                THEN 'ObfusKey'
            WHEN 19521
                THEN 'Asymmetric Key Login'
            WHEN 19523
                THEN 'Certificate Login'
            WHEN 19538
                THEN 'Role'
            WHEN 19539
                THEN 'SQL Login'
            WHEN 19543
                THEN 'Windows Login'
            WHEN 20034
                THEN 'Remote Service Binding'
            WHEN 20036
                THEN 'Event Notification on Database'
            WHEN 20037
                THEN 'Event Notification'
            WHEN 20038
                THEN 'Scalar SQL Function'
            WHEN 20047
                THEN 'Event Notification on Object'
            WHEN 20051
                THEN 'Synonym'
            WHEN 20549
                THEN 'End Point'
            WHEN 20801
                THEN 'Adhoc Queries which may be cached'
            WHEN 20816
                THEN 'Prepared Queries which may be cached'
            WHEN 20819
                THEN 'Service Broker Service Queue'
            WHEN 20821
                THEN 'Unique Constraint'
            WHEN 21057
                THEN 'Application Role'
            WHEN 21059
                THEN 'Certificate'
            WHEN 21075
                THEN 'Server'
            WHEN 21076
                THEN 'Transact-SQL Trigger'
            WHEN 21313
                THEN 'Assembly'
            WHEN 21318
                THEN 'CLR Scalar Function'
            WHEN 21321
                THEN 'Inline scalar SQL Function'
            WHEN 21328
                THEN 'Partition Scheme'
            WHEN 21333
                THEN 'User'
            WHEN 21571
                THEN 'Service Broker Service Contract'
            WHEN 21572
                THEN 'Trigger on Database'
            WHEN 21574
                THEN 'CLR Table-valued Function'
            WHEN 21577
                THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
            WHEN 21581
                THEN 'Service Broker Message Type'
            WHEN 21586
                THEN 'Service Broker Route'
            WHEN 21587
                THEN 'Statistics'
            WHEN 21825
                THEN 'User'
            WHEN 21827
                THEN 'User'
            WHEN 21831
                THEN 'User'
            WHEN 21843
                THEN 'User'
            WHEN 21847
                THEN 'User'
            WHEN 22099
                THEN 'Service Broker Service'
            WHEN 22601
                THEN 'Index'
            WHEN 22604
                THEN 'Certificate Login'
            WHEN 22611
                THEN 'XMLSchema'
            WHEN 22868
                THEN 'Type'
            ELSE 'Hmmm???'
            END AS ObjectType
    FROM [fn_trace_gettable](CONVERT(VARCHAR(150), (
                    SELECT TOP 1 value
                    FROM [fn_trace_getinfo](NULL)
                    WHERE [property] = 2
                    )), DEFAULT) T
    INNER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
    INNER JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
        AND v.subclass_value = t.EventSubClass
    WHERE TE.NAME IN (
            'Object:Created'
            ,'Object:Deleted'
            ,'Object:Altered'
            )
        -- filter statistics created by SQL server                                         
        AND t.ObjectType NOT IN (21587)
        -- filter tempdb objects
        AND DatabaseID <> 2
        -- get only events in the past 24 hours
        AND StartTime > DATEADD(HH, - 24, GETDATE())
    ORDER BY t.StartTime DESC;
    
  2. Digging into Transaction log -- More advance ! Note the use of undocumented fn_dblog.

    • Find out the transaction ID

      SELECT [Current LSN]
      ,Operation
      ,[AllocUnitName]
      ,[Lock Information]
      ,[Transaction ID]
      ,[Description]
      ,[Begin Time]
      ,[Transaction Name]
      ,[Transaction SID]
      FROM fn_dblog(NULL, NULL)
      WHERE [Operation] = N'LOP_DELETE_ROWS'
      AND [AllocUnitName] = N'sys.sysschobjs.clst'
      --- change here for the object name you think got dropped/deleted
      --- can be table, stored procedure, etc
      AND CHARINDEX(cast(N'ABOUT_TO_DELETE' AS VARBINARY(4000)), [Log Record]) > 0 
      
    • Now find out the transaction based on the above transaction ID:

                 SELECT [Current LSN]
                 ,Operation
                 ,[AllocUnitName]
                 ,[Lock Information]
                 ,[Transaction ID]
                 ,[Description]
                 ,[Begin Time]
                 ,[Transaction Name]
                 ,SUSER_SNAME([Transaction SID]) as CULPRIT_USER -- WE Got the Cluprit!!
               FROM fn_dblog(NULL, NULL)
               WHERE [Transaction ID] = N'0000:0000026c';
      

    enter image description here

Excellent References :

share|improve this answer
    
Using your 2nd query, if there was no result, does it mean that *.trc files has been recycled? –  Babak Apr 22 at 9:59
    
Yes that might be the case. You can use wmi event notification to trigger a trace into a table as well. If you have t-log backups then you can dig in them as well. –  Kin Apr 22 at 12:05
add comment

For the future you can use a database trigger to log the DDL events.

SQL Server DDL Triggers to Track All Database Changes

share|improve this answer
1  
You can use extended events/audits too. –  Kenneth Fisher Apr 21 at 16:53
add comment

For the stored procedure that has already been deleted, the only option is to dig into the online transaction log or transaction log backups (in case the database is in the full recovery model).

The above mentioned fn_dblog provides the requested info only if the transaction is still in the online transaction log. To read transaction log backups, use fn_dump_dblog. Check out Paul Randal's article:

Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN

To read the transaction logs (online, detached, and transaction log backups), besides using undocumented functions, you can use a third party tool such as ApexSQL Log. It can show who and when deleted the stored procedure, and will also provide the script to re-create it.

enter image description here

In future, you can also use the SQL Server Audit feature, just make sure you have specified all events you want to audit.

Disclaimer: I work for ApexSQL as a support engineer

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.