CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3252329
user3252329

Reputation: 15

Schema Changes history using t-sql

Is there any DMV or query to track the ddl changes to objects on sql server 2008r2 that incudes the LOGINNAME and SERVERNAME of the USER who altered the specific object

I'm trying to create a monitoring tool that includes this feature. Please help!

Thank you very much!

Upvotes: 0

Views: 2197

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can create DDL trigger for your database like:

CREATE TABLE [dbo].[ddl_log](
    [data] [XML] NULL,
    [systemuser] [NVARCHAR](255) NULL,
    [hostname] [NVARCHAR](255) NULL,
    [date] [DATETIME] NULL,
    [EventType] [NVARCHAR](255) NULL,
    [SPID] [SMALLINT] NULL,
    [SchemaName] [NVARCHAR](255) NULL,
    [ObjectName] [NVARCHAR](255) NULL,
    [ObjectType] [NVARCHAR](255) NULL,
    [CommandText] [NVARCHAR](MAX) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TRIGGER [ddl_log_all]
ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
    INSERT INTO ddl_log (data, systemuser, hostname, date, EventType, SPID, SchemaName, ObjectName, ObjectType, CommandText)
    SELECT 
        EVENTDATA(), SYSTEM_USER, HOST_NAME(), GETDATE(), 
        EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)'),
        EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','smallint'),
        EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)'),
        EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'),
        EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)'),
        EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO

ENABLE TRIGGER [ddl_log_all] ON DATABASE
GO

Then any change will be logged in table ddl_log.

Upvotes: 3

Related Questions