Tuesday, August 25, 2015

Who modified my tables: tracking SQL Server table changes

What is the easiest way of capturing changes on tables? I was asked this question and what immediately came to my mind was DDL Triggers. Though this is not a new feature of SQL Server, it is still unknown to many and various ways are implemented for capturing changes.

DDL Trigger can be implemented for capturing almost all DDL changes. Here is the code for capturing changes made to tables;

USE AdventureWorks2014;
GO

-- Creating a table for holding changes
CREATE TABLE TableChanges 
(
 ChangedDate datetime,
 EventType nvarchar(100), 
 TableName nvarchar(100), 
 LoginName nvarchar(100), 
 SQLCommand nvarchar(2000));
GO

-- Creating the DDL trigger for tables
CREATE TRIGGER CaptureTableChanges 
ON DATABASE 
WITH EXECUTE AS 'dbo'
FOR ALTER_TABLE, DROP_TABLE 
AS

 DECLARE @data XML

 SET @data = EVENTDATA();

 INSERT TableChanges 
  (ChangedDate, EventType, TableName, LoginName, SQLCommand) 
   VALUES 
  (getdate(), 
  @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)'),
  @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(100)'),
  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')
  ) ;
GO

-- Creating a login and user for testing
CREATE LOGIN Jack WITH PASSWORD = 'Pa$$w0rd', CHECK_EXPIRATION = OFF, CHECK_POLICY=OFF;
GO
CREATE USER Jack FOR LOGIN Jack;
GO
ALTER ROLE db_ddladmin ADD MEMBER Jack;


-- Use a different window for this
-- Connect using Jacks credentials and execute
ALTER TABLE Production.Product
 ALTER COLUMN Color nvarchar(20);
GO

-- Check captured changes
SELECT * FROM TableChanges ;
GO

No comments: