Sql-server – Log Sql Server Transactions (Queries + username)

sql servertransaction-log

I need to see which user has made what query with a lookback of ~90(?) days.

I have activated change tracking on the database with 90 days.

I have found several solutions:

SELECT 
    deqs.*, 
    dest.*, 
    deqs.last_execution_time AS [Time], 
    dest.TEXT AS [Query] 
FROM 
    sys.dm_exec_query_stats AS deqs 
CROSS APPLY
    sys.dm_exec_sql_text(deqs.sql_handle) AS dest 
ORDER BY
    deqs.last_execution_time DESC

But this query doesn't give me any information about the user who made the query.

Then I got this query

SELECT 
    * 
FROM 
    FN_TRACE_GETTABLE('E:\MSSQLServer\MSSQL12.DBNAME\MSSQL\Log\log_29.trc', default) 
WHERE 
    TextData IS NOT NULL 
ORDER BY 
    TransactionID DESC

This give me the username but no statements about DELETE/UPDATE etc.

I read about the SQL Profiler but I can't copy/read the _log.mdf-files because it is always in use.

I need a tip what s the right way to do this.. :/

Best Answer

Change tracking provides an efficient, light-weight data change tracking mechanism. In other words, this only tracks UPDATE, INSERT, DELETE statements; it does not track SELECT statements. It does not track who made the change.

If you truly need to track all DML and DDL statements made against the server, you'll need to implement an Extended Events monitoring solution, but be aware for a server that is heavily used, you will likely affect performance in a negative way.

If you only are interested in DML changes, then change tracking will allow you to understand what changes were made to the data using the CHANGETABLE T-SQL statement. This sample code shows how that works. The code drops any database named ChangeTrackingTest first, then creates that same database with a single table that has change tracking enabled. 

USE master;
GO
DROP DATABASE ChangeTrackingTest
GO
CREATE DATABASE ChangeTrackingTest;
ALTER DATABASE ChangeTrackingTest
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO
USE ChangeTrackingTest;
GO
CREATE TABLE dbo.t
(
    id int NOT NULL
        PRIMARY KEY
        CLUSTERED
);
ALTER TABLE dbo.t ENABLE CHANGE_TRACKING;

INSERT INTO dbo.t (id) VALUES (1);
UPDATE dbo.t SET id = 2 WHERE id = 1;

SELECT current_id = t.id
    , ctc.*
FROM dbo.t t
    CROSS APPLY CHANGETABLE(CHANGES dbo.t, NULL) ctc
GO

The results:

╔════════════╦════════════════════╦═════════════════════════════╦══════════════════════╦════════════════════╦════════════════════╦════╗
║ current_id ║ SYS_CHANGE_VERSION ║ SYS_CHANGE_CREATION_VERSION ║ SYS_CHANGE_OPERATION ║ SYS_CHANGE_COLUMNS ║ SYS_CHANGE_CONTEXT ║ id ║
╠════════════╬════════════════════╬═════════════════════════════╬══════════════════════╬════════════════════╬════════════════════╬════╣
║          2 ║                  2 ║                           1 ║ D                    ║ NULL               ║ NULL               ║  1 ║
║          2 ║                  2 ║                           2 ║ I                    ║ NULL               ║ NULL               ║  2 ║
╚════════════╩════════════════════╩═════════════════════════════╩══════════════════════╩════════════════════╩════════════════════╩════╝

Cleanup:

USE master;
GO
DROP DATABASE ChangeTrackingTest
Related Question