How to log MSSQL DDL operations

How to log create, update, delete operations with any SQL objects

This script is creating a trigger that log

CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE , CREATE_VIEW, ALTER_VIEW, DROP_VIEW,CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE , CREATE_VIEW, ALTER_VIEW, DROP_VIEW, DDL_SYNONYM_EVENTS, CREATE_INDEX, DROP_INDEX, ALTER_INDEX, CREATE_ASSEMBLY, ALTER_ASSEMBLY, DROP_ASSEMBLY

operations that will be write info about this operation into dbo.master.DDLEvents_Log


USE master;
GO

CREATE TABLE dbo.DDLEvents_Log
(
EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
EventType NVARCHAR(64),
EventDDL NVARCHAR(MAX),
EventXML XML,
DatabaseName NVARCHAR(255),
SchemaName NVARCHAR(255),
ObjectName NVARCHAR(255),
HostName VARCHAR(64),
IPAddress VARCHAR(32),
ProgramName NVARCHAR(255),
LoginName NVARCHAR(255)
);
GO

CREATE TRIGGER [Safety_DDLTrigger]
ON ALL SERVER
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE , CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
DDL_SYNONYM_EVENTS, CREATE_INDEX, DROP_INDEX, ALTER_INDEX, CREATE_ASSEMBLY, ALTER_ASSEMBLY, DROP_ASSEMBLY
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@EventData XML = EVENTDATA();

DECLARE
@ip VARCHAR(32) =
(
SELECT client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
);

INSERT master.dbo.DDLEvents_Log
(
EventType,
EventDDL,
EventXML,
DatabaseName,
SchemaName,
ObjectName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
@EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME();
END
GO

____

AskDevops —> https://t.me/ithangouts

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *