locked
SQL Server Object Modifications RRS feed

  • Question

  • Hi,

    I am using below query to for SQL Server Object Modifications. 

    How to modify this query to include user name (who modified object) ,user machine name and date/time?

    CREATE TRIGGER object_changes
    ON DATABASE
    FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_VIEW,DROP_VIEW,ALTER_VIEW,CREATE_PROCEDURE,DROP_PROCEDURE,ALTER_PROCEDURE
    AS 
       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,@TsqlCommand)
     
       EXEC msdb.dbo.sp_send_dbmail  
       @profile_name = 'SupportDBA',  
       @recipients = 'abc@xyz..com',  
       @body =@BodyMsg,
       @subject = 'The following object(s) was/were changed';
    GO

    Monday, January 14, 2019 7:05 PM

Answers

  • add these two:

    @usermodified = CURRENT_USER,

    @ModifedDate = getDate(),

    • Marked as answer by VijayKSQL Thursday, January 24, 2019 3:03 PM
    Monday, January 14, 2019 7:24 PM