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.

Answers 4

  • 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
    FROM   sys.traces t
           CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),
                                                           CHARINDEX('\', REVERSE(t.path)), 
                                                 ) + 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*/ )

  • Martin already pointed toward the best avenue, the administrative audit trace which is usually on (unless it has been explicitly disabled). If you cannot find the info in the admin trace (was disabled or it had recycled) you can retrieve the info from the log backups. Since is a production DB, I assume you have a regular backup cycle, with periodic full backup and log backups. You will need to restore, on a separate server, the database to around the time of the incident so that the DDL is in the current restored log. Then is a simple matter of using fn_dblog() and inspecting the log.

    One way is to go by transaction begin operations:

    select [Begin Time], [Transaction Name], [Transaction SID], * 
    from fn_dblog(null, null)
    where Operation = 'LOP_BEGIN_XACT';

    If the ALTER VIEW was issued in a standalone transaction (ie. not surrounded by BEGIN TRANSACTION/COMMIT) then it will start a transaction named CreatProc transaction. Look for it, and the [Transaction SID] is the login SID you want.

    Another possibility is to look for the transaction that acquired a SCH_M on the view you want:

    select [Lock Information], * 
    from fn_dblog(null, null)
    where [Lock Information] like '%' + cast(object_id('...') as varchar(10))+'%'
    and [Lock Information] like '%LOCK_SCH_M%'

    Note that if the view was changed by DROP followed by CREATE the object id was likely changed, but at least you will get the transaction that last did the CREATE (the current object id of the view in the restored db). With the transaction id you go back and retrieve the begin transaction info:

    select [Begin Time], [Transaction Name], [Transaction SID], *
    from fn_dblog(null, null)
    where [Transaction ID] = '...'
    and Operation = 'LOP_BEGIN_XACT';

    The [Transaction SID] is, again, your guy. Use SUSER_SNAME to retrieve the login name from the login SID. If the SID is 0x01 it means the login was sa, which means any individual which knows the sa password could had done it.

  • No, unless you logged it via a DDL trigger or such

    You want to look at who as ALTER rights in that database, or membership of sysadmin/db_owner/ddl_admin role. This would be better as a general review rather than a witch hunt. There are probably other people with rights to make unapproved and unauthorised changes too

  • If you haven't already, you might want to check out the Schema Changes History report available in SQL Server Management Studio. It looks like SQL Server logs changes by default (default trace) and you should be able to view that data via this report. The only unfortunate thing is that these trace files are automatically deleted/rolled over as time goes on, so the data may already be gone. Good luck!

Related Questions