Check who performed insertion in table on MS SQL Server

Question

I would like to check which user performed insertions in specific table on MS SQL Server 2008. I am aware of some logging info being stored, but I don't know how to access it. I would be grateful with provided specific info on my question and also with general pointer where and what to look for if I ever need other information too.

Thanks!

http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL — sumit, Dec 10 '13 at 10:29
So it is not possible to do so unless I have previously created triggers and audit tables? I was asking if it is possible to check logs for such information but I guess that this is not possible then? — MK86, Dec 10 '13 at 10:53
Not giving us exactly the logging process that takes place in your db, make it quite difficult to figure out how to help you. — Christos, Dec 10 '13 at 10:53

Martin Smith · Accepted Answer · 2013-12-13T18:07:20.267

Are you talking about retrieving this from the transaction logs?

This isn't ideal as you have no guarantee that the relevant rows will still be available in the active log and it is less efficient to query but something like the below would do it (returns USER_NAME() rather than the likely more useful SUSER_NAME() though).

Change dbo.X to your actual table name.

DECLARE @allocation_unit_ids TABLE (
  allocation_unit_id BIGINT PRIMARY KEY )

INSERT INTO @allocation_unit_ids
SELECT allocation_unit_id
FROM   sys.allocation_units au
       JOIN sys.partitions p
         ON au.container_id = CASE
                                WHEN au.type IN ( 1, 3 ) THEN p.hobt_id
                                WHEN au.type = 2 THEN p.partition_id
                              END
WHERE  p.object_id = OBJECT_ID('dbo.X');

WITH L1
     AS (SELECT [Transaction ID],
                [Begin Time],
                [End Time],
                [Transaction SID],
                CASE
                  WHEN Operation = 'LOP_INSERT_ROWS'
                       AND AllocUnitId IN (SELECT allocation_unit_id
                                           FROM   @allocation_unit_ids) THEN 1
                END AS I
         FROM   sys.fn_dblog(NULL, NULL) l),
     L2([Transaction ID], TransactionBegin, TransactionEnd, sid)
     AS (SELECT [Transaction ID],
                MAX([Begin Time]),
                MAX([End Time]),
                MAX([Transaction SID])
         FROM   L1
         GROUP  BY [Transaction ID]
         HAVING 1 = MAX(I))
SELECT TransactionBegin,
       TransactionEnd,
       p.name AS PrincipalName
FROM   L2
       JOIN sys.database_principals p
         ON p.sid = L2.sid

Marko Krstic · Answer 2 · 2013-12-23T17:02:06.923

You can try out ApexSQL Log to read the transaction log. Trial is fully functional for 14 days, add available data sources and filter only INSERT statements. Bottom line is that you can allows viewing transaction log data in read-friendly format

Also you can check out undocumented function fn_dblog or DBCC LOGINFO command.

EDIT: Forgot to give you link to the tool: http://www.apexsql.com/sql_tools_log.aspx

Disclaimer: I work for ApexSQL as support engineer

Jirka Hubáček · Answer 3 · 2013-12-13T17:51:04.940

The simplest solution is to create a computed column with SUSER_SNAME(). However this computed column would reflect even updates (but on the other hand it is all about adding a column)

Try this example in your test database:

CREATE TABLE [dbo].[logging_test]
(
  [logging_test_id] [int] IDENTITY(1, 1)
                          PRIMARY KEY
                          NOT NULL ,
  [SomeData] [nvarchar](500) NULL ,
  [Audit_UpdatedBy] AS ( SUSER_SNAME() ) ,
  [Audit_InsertedBy] NVARCHAR(200) NULL
)

INSERT INTO [dbo].[logging_test] ( [SomeData] ) VALUES ( 'foo' )

SELECT * FROM [dbo].[logging_test]

Or you could create after trigger that would update Audit_InsertedBy column (run everything from CREATE TRIGGER to END ... code splits here for some reason)

CREATE TRIGGER dbo.logging_test_InsertTrigger ON dbo.logging_test
AFTER INSERT

AS BEGIN
SET NOCOUNT ON;

    UPDATE  dbo.logging_test
    SET     Audit_InsertedBy = SUSER_NAME()
    FROM    dbo.logging_test lt
            INNER JOIN INSERTED i ON i.logging_test_id = lt.logging_test_id

END

Check who performed insertion in table on MS SQL Server

3 Answers3

Linked