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.

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 NJan 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 SmithJan 17 '12 at 11:13

Thanks martin, but the result still remains the same
–
Romil NJan 17 '12 at 11:19

@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 SmithJan 17 '12 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.

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:

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:

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.

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!