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.

4 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*/ )
  • Thanks Martin, I executed query by replacing 'FOO' with my view but that didn't return anything. Any idea why that happen? I executed not on server, though
    – xorpower
    Jan 17, 2012 at 11:08
  • 1
    @Xorpower - I edited it to handle the Object:Created event as well incase the view was dropped and created rather than altered. Not sure what you mean by not executing on server? You need to be connected to the correct instance of course but doesn't matter where the connection comes from as long as you have permissions. Jan 17, 2012 at 11:13
  • Thanks martin, but the result still remains the same
    – xorpower
    Jan 17, 2012 at 11:19
  • 1
    @Xorpower - What does the following return? Jan 17, 2012 at 11:21
  • 3
    @Xorpower - Well looks like the trace has rolled over then and you've lost details of anything older than about 11 hours. The default trace only keeps 5 files and then deletes older ones. You might want to check on the file system on the server the folder just to check this is definitely the case. You can get the folder path from SELECT path FROM sys.traces where is_default=1 Jan 17, 2012 at 11:26

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.

  • 2
    Nice tip on reading the log files. This comes in handy if someone disabled the default traces. Feb 16, 2012 at 17:01
  • What if the Transaction SID is null ? Jun 16, 2016 at 7:59
  • @evictednoise please post the relevant log records (in a separate question) . It can be more that one reason and the log records would help determine the actual cause. Jun 16, 2016 at 11:00

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!

  • Whoops, never mind. I see Martin Smith already referenced this report in his answer. I'll leave this here though in case any of the links are helpful.
    – Mark Madej
    Jan 18, 2015 at 18:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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