21

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.

Martin Smith
  • 84,644
  • 15
  • 245
  • 333
xorpower
  • 577
  • 1
  • 8
  • 18

4 Answers4

37

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
       ,LoginName
       --,f.*
FROM   sys.traces t
       CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),
                                                       CHARINDEX('\', REVERSE(t.path)), 
                                                       260)
                                             ) + 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 Smith
  • 84,644
  • 15
  • 245
  • 333
  • 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 '12 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. – Martin Smith Jan 17 '12 at 11:13
  • Thanks martin, but the result still remains the same – xorpower Jan 17 '12 at 11:19
  • 1
    @Xorpower - What does the following return? – Martin Smith Jan 17 '12 at 11:21
  • First Column returns 2012-01-17 06:24:09.820 and Second Column returns 2012-01-16 17:32:16.803 – xorpower Jan 17 '12 at 11:25
  • 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 – Martin Smith Jan 17 '12 at 11:26
19

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%'
go

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.

Remus Rusanu
  • 51,846
  • 4
  • 95
  • 172
6

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

gbn
  • 69,809
  • 8
  • 163
  • 243
0

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!

Mark Madej
  • 101
  • 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 '15 at 18:52