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.

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.

share|improve this question
add comment

3 Answers 3

up vote 24 down vote accepted

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*/ )
share|improve this answer
    
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 –  Romil N 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 –  Romil N Jan 17 '12 at 11:19
1  
@Xorpower - What does the following return? –  Martin Smith Jan 17 '12 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 –  Martin Smith Jan 17 '12 at 11:26
show 2 more comments

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.

share|improve this answer
2  
Nice tip on reading the log files. This comes in handy if someone disabled the default traces. –  StanleyJohns Feb 16 '12 at 17:01
add comment

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

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.