SQL SERVER DBA, Linux and Azure: Audit SQL modifications and get notified in SQL Server

Audit SQL modifications and get notified in SQL Server

USE [DBA]
GO

/****** Object:  DdlTrigger [object_change_notification]    Script Date: 11/11/2019 8:24:11 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [object_change_notification]
ON DATABASE
FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_VIEW,DROP_VIEW,ALTER_VIEW,CREATE_PROCEDURE,DROP_PROCEDURE,ALTER_PROCEDURE
AS

   Declare @Hostname varchar(20) = HOST_NAME()
   DECLARE @sys_usr char(30) SET @sys_usr = SYSTEM_USER
   Declare @executiontime datetime =getdate()
   DECLARE @data XML = EVENTDATA()
   DECLARE @eventType nvarchar(100)= CONCAT ('EVENT: ',@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),+ CHAR(13))
   DECLARE @TsqlCommand nvarchar(2000)=CONCAT('COMMAND:   ',@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'))
   DECLARE @BodyMsg nvarchar(2100)=CONCAT(@eventType , @sys_usr, @Hostname ,@executiontime , @TsqlCommand)

   EXEC msdb.dbo.sp_send_dbmail  
   @profile_name = 'ajaymail',  
   @recipients = 'ajeyudu.eee@gmail.com',  
   @body =@BodyMsg,
   @subject = 'The following object(s) was/were changed';



GO

ENABLE TRIGGER [object_change_notification] ON DATABASE
GO


No comments:

Post a Comment