Forensic Analysis Of Users Who Performed Certain Transactions On Database

Learn about know forensic analysis of SQL Log

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)

Forensic Analysis of Users Who Performed Certain Transactions on Database
  • After this, you will get Transaction Name and Transaction SID.

Forensic Analysis of Users Who Performed Certain Transactions on Database

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

Forensic Analysis of Users Who Performed Certain Transactions on Database
  • In the Results section, you will get the user who performed the transaction

Forensic Analysis of Users Who Performed Certain Transactions on Database

 

What If This Solution Does Not Work For You?


SQL Log Forensics is another, even simpler, way to detect a suspected user who has made certain transactions on a database. The software is designed in such a manner that it allows reading as well as opening the SQL Server log file transactions without any technical assistance. It displays a preview of LDF file activity along with Transaction, Time, Transaction Name, and Query. With this, users can analyze all the transactions such as INSERT, UPDATE, DELETE etc., and it provides support to multiple data types like Datetime2, sql_variant, hierarchyid, atetimeoffset, and geometry. The tool provides support to fetch and display the records from Live database and recover modified records if the database is in Simple Recovery Mode. Below are the steps to forensically analyze the users who performed certain transactions on a database:
  • Open SQL Log Analyzer Tool and click on Open to add Log file to the software

Forensic Analysis of Users Who Performed Certain Transactions on Database
  • 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

Forensic Analysis of Users Who Performed Certain Transactions on Database
  • Click OK and once the scanning process gets completed you will get details like No. of records, updates, deletes. Click OK

Forensic Analysis of Users Who Performed Certain Transactions on Database
  • 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.

Forensic Analysis of Users Who Performed Certain Transactions on Database

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.

Forensic Analysis of Users Who Performed Certain Transactions on Database
 

Conclusion

 
Every SQL Server uses a transaction log to record all the transactions and the database modifications. Hence, the transaction log is a critical component of the SQL database. There come a few situations, when any user makes some changes in the master database table’s information, and after detecting the changes, DBAs might need to find out that user. Hence, to make this task simpler and more efficient we have come up with this blog. Here, we have discussed two different solutions for the forensic analysis of users who performed certain transactions on a database.