Every DBA and developer strives to be in control of the databases, servers, and SQL data. However, sometimes the situation can get out of control and unusual things start to happen. For instance; someone has modified the data in a sensitive table. Or, some clueless person has deleted a table or even a record of another table that should not be deleted. After that, the admin attempts to verify the code as much as possible. The situation becomes even worse when the admin verifies the permissions in the table/ column and realizes that no unauthorized deletion was allowed but, something is obviously wrong.
Thus, in any database, the lack of transaction management basically results in performance and contention issues. As the number of users accessing the SQL database increases, it becomes essential to use transactions efficiently. This technical guide describes complete information on how to do a forensic analysis of users who performed certain transactions on a database in SQL Server.
Inspect SQL Server Database And See Who Made Transactions
In order to find out the users who executed transactions on a particular database, we will use the undocumented function "fn_dblog". This command captures and tracks database activities performed by all the users who made certain changes or deleted data from a table or column of the database.
To audit your SQL Server database and see who removes the data and when, follow the instructions below.
First of all, execute the following query on the existing database in which certain transactions are performed,
select [Transaction Name], [Transaction SID] from fn_dblog(null,null)

After this, you will get Transaction Name and Transaction SID.

Now, you have to convert the transaction SID hexadecimal value into the text so that you can detect the actual name of the user.
For this, copy the hexadecimal value from Transaction SID column for the suspected transaction and then paste it in the SUSER_SNAME () function. Now, execute the query

In the Results section, you will get the user who performed the transaction







Andre NeumannPosted Aug 2, 2021, 3:10 PM
Thanks for your useful information. I'd like to add that there is one more possible solution in this case. Hope that it will be helpful https://www.devart.com/dbforge/sql/transaction-log/
Vinay Kumar GuptaPosted Jan 4, 2019, 11:32 AM
Outstanding information and thanks to you for valuable information.