Alerts for SQL Server security events by serverku

Security is the main thing for servers and SQL servers and we need to trace it for security management. Have to need track who is using it, what they are doing and when they performed. For SQL Server 2005 and later versions we have some DDL events which can help us to achieve this. So let us catch it up. But before going ahead, please visit DDL Trigger and DDL Auditing in SQL Server 2005 and Logon trigger in SQL Server for more information about DDL trigger.
I am presenting here for DDL triggers fire for security events. We can also log all the event data in the table, but I want to describe this post to get al alert for this security event's occurrence. To implement I am creating a DDL trigger in the master database and evaluate for some scenario like login creation, Add server roles and drop logins. You can find the trigger created for the same as following,

CREATE TRIGGER [Trg_TrackLoginManagement]
ON ALL SERVER
FOR 
 DDL_SERVER_SECURITY_EVENTS
AS
BEGIN
SET NOCOUNT ON

DECLARE @data xml,
              @EventType varchar(100),
              @EventTime datetime,
              @ServerName varchar(100),
              @AffectedLoginName varchar(100),
              @WhoDidIt varchar(100),
              @EmailSubject varchar(500),
              @EmailBody varchar(800),
              @EmailRecipients varchar(300),
              @TSQL varchar(4000)
 
SET @EmailRecipients = 'prajapatipareshm@gmail.com'
 
SET @data = EVENTDATA()
SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
SET @EventTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
SET @ServerName = @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(100)')
SET @AffectedLoginName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)')
SET @WhoDidIt = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(100)')
SET @TSQL = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(4000)')

SET @EmailSubject =  @EventType + ' occured by ' + @WhoDidIt + ' on ' +
      @ServerName + ' occured at: ' + convert(Varchar, @EventTime) 
SET @EmailBody = @TSQL 

EXEC msdb.dbo.sp_send_dbmail  
    @recipients = @EmailRecipients
  , @subject = @EmailSubject  
  , @body = @EmailBody  
  , @profile_name = '<ProfileName>' -- Put profile name here
  , @body_format = 'HTML' ;  

END

We are capturing some data which is full information of login name , the events occurred and the date on which it occurred and who did. I collected some snaps after performing and testing some scenario for creating a login, assigning server roles and finally deleting created login after testing, Let me share here.

Above are the alert emails which I received for the events happened to create login, server role assignment and after all deleting the login. If we do not want to continue receiving the alerts for change, then it DDL trigger on the server can be disabled with following statement.

DISABLE TRIGGER [Trg_TrackLoginManagement] ON ALL SERVER
GO

Did you configure any alerts for such DDL events? Share your thoughts here.