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.
6 thoughts on “More on using Transaction SID from the transaction log”
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.
Gotta love all those great questions that come out of the IE classes!
Can I also track the user machine ip for particular transactions?
Nope, not form the log itself – you’d need to use XEvents to get the client machine name.
@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.
Are you looking at the LOP_BEGIN_XACT log record? That’s the only place that a SID will show up.