I’ve answered two such questions recently, one on the Spiceworks forum and one on #sqlhelp on Twitter where I was able to find pertinent information in the transaction log. Key to this was determining that the column [Transaction SID] is the security identifier (SID) of the login/user that performed the action. I can pass that in to the SUSER_SNAME() function to get the user name.
SQL Server likes to use named transactions internally. You can find a lot of useful information just by looking at the [Transaction Name] column. Unfortunately, the names they use are not always the most informative. For example, if you grant, revoke, or deny permissions to a user, you can find a named transaction for each permission change in the transaction log; however, each of those transactions will be named Grant. With a simple query, I can find entries for permission changes, but I cannot tell for sure which of the changes was made.
As I have said above, I used transaction log spelunking to answer two questions recently. The first question was wanting to know who was changing permissions on logins. The second question was asking who was disabling jobs on the server.
The T-Log Solutions
Solution for Finding Who Altered Permissions on Logins
This one was quite simple. I simply look for transactions named CREATE USER, ADD/DROP ROLE MEMBER, or GRANT.
Select SUSER_SNAME([Transaction SID]) As Changer, [Transaction Name] From fn_dblog(null, null) Where [Transaction Name] In ('CREATE USER', 'ADD/DROP ROLE MEMBER', 'GRANT');
Solution for Finding Who Altered a Job or a Job Schedule
This one was a little trickier because the name of the transactions were too generic to be relied upon. Changing a job resulted in a transaction named “user_transaction” and changing a schedule yielded a transaction named “update”. I was able to find the transactions I wanted by looking at the AllocUnitName (allocation unit name or name of object allocated a page) column to identify when the clustered index of the dbo.sysjobs and dbo.sysschedules tables were modified. A single transaction can have many log records, and in this case, the log record with the allocation unit name were different than the log record containing the transaction SID. First step is to identify the transactions that modify the desired objects and then query all log records with those transaction IDs.
With TranIDs As (Select [Transaction ID] From fn_dblog(null, null) Where AllocUnitName = 'dbo.sysjobs.clust' Or AllocUnitName like 'dbo.sysschedules.%') Select MAX(SUSER_SNAME(DBL.[Transaction SID])) As Changer, MAX(DBL.AllocUnitName) As AllocationName From fn_dblog(null, null) DBL Inner Join TranIDs TI On TI.[Transaction ID] = DBL.[Transaction ID] Group By DBL.[Transaction ID];
Now, I’m looking for more reasons to dig into the transaction log to see what else I can find.