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

What If This Solution Does Not Work For You?

• Open SQL Log Analyzer Tool and click on Open to add Log file to the software

• Now, choose appropriate option to add the file from Online DB Option and Offline DB Option

• Suppose you have selected Online Database Option so the tool will let you select the Server Name from the available list and if the Server Name is unavailable then, enter it manually. Choose Authentication and click on the drop-down arrow to Select Database

• Click OK and once the scanning process gets completed you will get details like No. of records, updates, deletes. Click OK

• Now, you can preview the selected log file and in the preview pane, you will find a table named Login Name.
  
  This table will display a complete list of all the users who made the transaction on the database and when. Apart from all this, you can check the Table Name, Transaction Name, and executed queries also.

You can select any of the Tables from the list to preview and analyze the corresponding log details of the operations performed on a database

• If you want to export the tables then, this also can be done with this tool as it provides the Export option.

Conclusion