Querying The Transaction Log Of A Database To Identify Activity By User In SQL Server
I recently ran into a scenario where a particular SQL account was executing ALTER TABLE
commands on a database. Since I had only recently put additional monitoring tools in place to look for this type of activity I wanted to research exactly what was going on to determine if this was normal behavior since the account in question is used by the application and has elevated permissions.
After doing some initial research I determined the best course of action was to query the previous day’s transaction log for the database and review all the activity being performed by the account in question.
If you’re not familiar with querying a transaction log the first thing you’ll need to be aware of is the fn_dblog()
function. This is an undocumented function which allows you to view transaction log records for the current database.
The fn_dblog()
function accepts two parameters, the starting Log Sequence Number and the ending Log Sequence Number, also called LSN. Both of these parameters can also be set to NULL
. Doing this will return every record in the log.
Though you can certainly call the function without specifying any particular columns within the SELECT
, I have called the most relevant columns in the example below which pertain to the type of information I am wanting to review.
USE [DATABASE NAME] -- Specify Database Name
SELECT
[Transaction Name],
[Description],
[Operation],
[Transaction ID],
[Transaction SID],
[Begin Time],
[End Time]
FROM fn_dblog(NULL, NULL)
The above script will return ALL RECORDS within the current database’s transaction long.
The following screenshot illustrates the type of results you may see.
Now that I can query the basic data from the transaction log I need to take this one step further in order to break it down by individual user.
In order to query by user, the first thing I need to find is the specific user’s Transaction SID. The easiest way to do this is to run the following query. You’ll want to insert the specific SQL or Domain account. In the example below I am retrieving the TSID of my SQL account.
/*Identify TSID by user account*/
SELECT SUSER_SID('jworthen')
GO
Every TSID is unique. The output will appear similar to the screenshot below.
Additionally, you can also determine a specific user account if you only have the user’s TSID by using the following script.
/*Identify user account by TSID*/
USE [master]
GO
SELECT SUSER_SNAME(0x0E5955DB6D3C784AB9916C839B8732F3) --Specify TSID
Now that I know the TSID of the account I can include an additional WHERE
clause in the query to only display transaction log records of that specific account.
USE [DATABASE NAME] -- Specify Database Name
SELECT
[Transaction Name],
[Description],
[Operation],
[Transaction ID],
[Transaction SID],
[Begin Time],
[End Time]
FROM fn_dblog(NULL, NULL)
WHERE [Transaction SID] =0x0E5955DB6D3C784AB9916C839B8732F3
--AND[Transaction Name] = 'ALTER TABLE'
Though currently commented out, I have also included an additional clause that can be used if you are looking for a particular type of transaction. The example above specifically looks for ALTER TABLE
events performed by the specified user, but you can change this based on the type of events that you see in the Transaction Name column.
You can also further modify the above script to query based on the Begin Time and End Time if you know when the event occurred.
This is merely the tip of the iceberg in terms of what you can do when calling the fn_dblog()
function to query a database’s transaction log. There is a wealth of data contained within the transaction log that can be queried when dealing with anything from rouge account behavior to performance tuning.