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)
Now, you have to convert the transaction SID hexadecimal value into the text so that you can detect the actual name of the user.
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:
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
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
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.