More on using Transaction SID from the transaction log

Back in 2012 I blogged about using fn_dblog and fn_dump_dblog to figure out the point at which something occurred that you’d like to restore to just before (e.g. a table drop). I also mentioned that you can use the SUSER_SNAME () function on on the [Transaction SID] column for the LOP_BEGIN_XACT log record of the operation to find out who performed the operation.

Yesterday in our IE2 Performance Tuning class in Tampa, someone asked me what the [Transaction SID] column would show if someone had run EXECUTE AS. As I wasn’t 100% certain, I decided to test and write a quick blog post.

First off I’ll set up a database and table to use:

USE [master];
GO
CREATE DATABASE [Test];
GO
ALTER DATABASE [Test] SET RECOVERY SIMPLE;
GO
USE [Test];
GO
CREATE TABLE [TestTable] ([c1] INT IDENTITY);
GO
INSERT INTO [TestTable] DEFAULT VALUES;
GO 5

Next I’ll create a Kimberly user for a SQL login, and a Katelyn user for a Windows login:

-- Create Kimberly login and user
CREATE LOGIN [KimberlyLogin] WITH PASSWORD = 'NiceWife';
CREATE USER [KimberlyUser] FOR LOGIN [KimberlyLogin];
EXEC sp_addrolemember N'db_owner', N'KimberlyUser';
GO

-- Create Katelyn user
CREATE USER [KatelynUser] FOR LOGIN [APPLECROSS\Katelyn];
EXEC sp_addrolemember N'db_owner', N'KatelynUser';
GO

Now I’ll delete a single row as me and each of the users and logins:

-- Delete as me
DELETE FROM [TestTable] WHERE [c1] = 1;
GO

-- Now delete as Kimberly user
EXECUTE AS USER = N'KimberlyUser';
DELETE FROM [TestTable] WHERE [c1] = 2;
REVERT;
GO

-- Now delete as Kimberly login
EXECUTE AS LOGIN = N'KimberlyLogin';
DELETE FROM [TestTable] WHERE [c1] = 3;
REVERT;
GO

-- Now delete as Katelyn user
EXECUTE AS USER = N'KatelynUser';
DELETE FROM [TestTable] WHERE [c1] = 4;
REVERT;
GO

-- Now delete as Katelyn login
EXECUTE AS LOGIN = N'APPLECROSS\Katelyn';
DELETE FROM [TestTable] WHERE [c1] = 5;
REVERT;
GO

Finally I’ll pull the [Transaction SID] for each of the delete operations and pass it into SUSER_SNAME ():

SELECT
	[Operation], [Transaction Name], [Transaction SID],
	SUSER_SNAME ([Transaction SID]) AS [WhoDidIt?]
FROM fn_dblog (NULL, NULL)
WHERE [Operation] = N'LOP_BEGIN_XACT'
AND [Transaction Name] = 'DELETE';
GO
Operation       Transaction Name  Transaction SID                                             WhoDidIt?
--------------- ----------------- ----------------------------------------------------------- -------------------
LOP_BEGIN_XACT  DELETE            0x0105000000000005150000003A5014D05A957BF8F5C8882EE8030000  APPLECROSS\paul
LOP_BEGIN_XACT  DELETE            0x9A9A69BEACF67E4994E2F2DEE35BC02F                          KimberlyLogin
LOP_BEGIN_XACT  DELETE            0x9A9A69BEACF67E4994E2F2DEE35BC02F                          KimberlyLogin
LOP_BEGIN_XACT  DELETE            0x0105000000000005150000003A5014D05A957BF8F5C8882EFE030000  APPLECROSS\Katelyn
LOP_BEGIN_XACT  DELETE            0x0105000000000005150000003A5014D05A957BF8F5C8882EFE030000  APPLECROSS\Katelyn

So the answer is that the log record contains the SID of who you’re executing as. The only way to tell who is really running the code would be through auditing.

Enjoy!

6 thoughts on “More on using Transaction SID from the transaction log

  1. Yeah, not really ideal. Auditing can get you the “original login” when using execute as (as opposed to the contextual account). I wrote a post here: http://www.sqlskills.com/blogs/kimberly/execute-as-and-an-important-update-your-ddl-triggers-for-auditing-or-prevention/ that shows how you can get the information from OriginalLoginName in the event (captured by a DDL trigger). If you want to get this through trace/profiler you use SessionLoginName.

    Have fun,
    nice wife

  2. @Paul I have used the above method to track who has deleted some records from production env but unfortunately, I am getting null Transaction SID.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.