CODEWITHSUNDEEP
sql-server
carlosm
carlosm

Reputation: 755

Check who/when updated a column in SQL Server

I have really hard time to answer this question to my colleagues, sometimes we have changes done on a table and we don't know who or when was changed, I have this query but it not really accurate sometimes.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'MYDB') --DB Name
AND OBJECT_ID=OBJECT_ID('MyTable') --Table Name

is there any other accurate way to answer this question?

Upvotes: 0

Views: 5888

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280645

If you're talking about who altered the structure of a table

If it was recent enough, it may still be captured in the rolling default trace:

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 164    -- Object:Altered
AND EventSubClass = 1
ORDER BY StartTime DESC;

If not, well, maybe it's time to invest in DDL triggers, source control / change management, not giving all of your colleagues enough permissions to change the database, or all of the above...

If you're talking about who changed data in a table

SQL Server doesn't track this data. You can look at a combination of DML triggers, SQL Audit, Change Tracking, Change Data Capture, Temporal Tables. Your choice will depend on your version and edition of SQL Server, how much data is changing, and which tables you care about tracking...

Upvotes: 6

Related Questions