![]() Saturday, Novem8:29:08 PM - To Top (69598)ĭoes the recovery model matters for reading that kind of log for drop column etc?įor me it is showing NULL in column name I did not user name who deleted. SELECT * FROM SUSER_SNAME(0x01), it displays error "invalid object name 'SUSER_SNAME', even SUSER_SNAME shows in pink color. Hi, When I excute query to obtain user name i.e. Of course, if we ask the question to my colleague it's none of them :) :)īut is it possible to get Table which has been dropped? Thanks for this great article, just one question is it possible to know who execute an update ?īecause our problem, we'd like to find who has updated SOME rows in database in enviroment of PRODUCTION ! Run the below SQL code to create a database and table. Finding a user who ran a DELETE statementīefore moving ahead, we will create a database and a table on which I will delete some data. ![]() But these changes are also tracked in the transaction log file of the database and we will use this to find the users in question. One way to find such users is with the help of the default trace, because the default trace captures and tracks database activity performed on your instance, but if you have a busy system the trace files may roll over far too fast and you may not be able to catch some of the changes in your database. I strongly suggest testing any undocumented functions in a lab environment first. This tip will help you track or find any unethical or an unwanted user who has dropped a table or deleted data from a table. Here we will use the same undocumented function "fn_dblog" to find any unauthorized or unapproved deletes or table drops. Before reading this tip, I recommend that you read the previous tip to understand how the transaction log file logs all database activity. They would need to be adopted to implement proper error handling etc.I have already discussed how to read the transaction log file in my last tip " How to read SQL Server Database Log file". Note: The supplied examples does not represent production quality scripts.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |