CJSmith dot me

I dump stuff I find useful here

Setup MS SQL Server Alerts via TSQL

Published / by Chris Smith / Leave a Comment

Amend TSQL to your environment, run the TSQL, restart the agent service and test (I find it easy to enter an incorrect username and password).

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,                              @databasemail_profile=N'<TSQL_MAIL_PROFILE>',                              @use_databasemail=1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Login Mismatch',                               @message_id=18456,                               @severity=0,                               @enabled=1,                               @delay_between_responses=0,                               @include_event_description_in=1,                              @job_id=N'00000000-0000-0000-0000-000000000000'GO

USE [msdb]
GO 
/****** Object:  Alert [Login failed : State 5]    Script Date: 24/03/2020 12:50:49 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Login failed : State 5',                               @message_id=0,                               @severity=0,                               @enabled=1,                               @delay_between_responses=0,                               @include_event_description_in=5,                              @category_name=N'[Uncategorized]',                              @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',                              @wmi_query=N'SELECT * FROM AUDIT_LOGIN_FAILED WHERE State = 1 OR State=  5 OR State =  8 OR State= 14 OR State=  38',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO USE [msdb]GO /****** Object:  Alert [Error 823]    Script Date: 24/03/2020 12:51:12 ******/EXEC msdb.dbo.sp_add_alert @name=N'Error 823',                               @message_id=823,                               @severity=0,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Error 824]    Script Date: 24/03/2020 12:51:33 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Error 824',                               @message_id=824,                               @severity=0,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Error 825]    Script Date: 24/03/2020 12:51:51 ******/EXEC msdb.dbo.sp_add_alert @name=N'Error 825',                               @message_id=825,                               @severity=0,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 016]    Script Date: 24/03/2020 12:52:05 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',                               @message_id=0,                               @severity=16,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Severity 017]    Script Date: 24/03/2020 12:52:14 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',                               @message_id=0,                               @severity=17,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 018]    Script Date: 24/03/2020 12:52:22 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',                               @message_id=0,                               @severity=18,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 019]    Script Date: 24/03/2020 12:52:34 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',                               @message_id=0,                               @severity=19,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Severity 020]    Script Date: 24/03/2020 12:52:44 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',                               @message_id=0,                               @severity=20,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 021]    Script Date: 24/03/2020 12:52:52 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',                               @message_id=0,                               @severity=21,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO   USE [msdb]GO /****** Object:  Alert [Severity 022]    Script Date: 24/03/2020 12:53:02 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',                               @message_id=0,                               @severity=22,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO  
USE [msdb]
GO 
/****** Object:  Alert [Severity 023]    Script Date: 24/03/2020 12:53:23 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',                               @message_id=0,                               @severity=23,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 024]    Script Date: 24/03/2020 12:53:32 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',                               @message_id=0,                               @severity=24,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'GO  USE [msdb]GO /****** Object:  Alert [Severity 025]    Script Date: 24/03/2020 12:53:52 ******/EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',                               @message_id=0,                               @severity=25,                               @enabled=1,                               @delay_between_responses=60,                               @include_event_description_in=1,                              @category_name=N'[Uncategorized]',                              @job_id=N'00000000-0000-0000-0000-000000000000'
GO 

Leave a Reply