My end goal is to be emailed when new object (schema, table, procedure etc.) is created / altered / dropped at one of my databases on SQL Server
I created a server-level audit, that writes to Windows application log, and a database audit specification at my database, that audits two below event classes:
Then I tested it, by creating a table, procedure, altering schema, etc. All those changes were recorded by audit into Application Log successfully, with Event ID = 33205
Next step, I created an Alert at SQL Server Agent, which is supposed to be raised on error number 33205. But this Alert is not working, it is not triggered when objects are changed at my database, and email is not sent
QUESTION: -- Could you help me to understand why alert is not triggered and not sending emails ?
p.s. I have other alerts based on particular error numbers in place, such as "Login Failed" or "Availability Group"-related which work perfectly fine, and shooting emails)
p.p.s. scripted audit / specification / alert below:
CREATE SERVER AUDIT [DB_Schema_And_Object_Changes_To_App_Log] TO APPLICATION_LOG WITH (QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '55c92648-fc06-47b7-b48d-3a613ba3b8aa') ALTER SERVER AUDIT [DB_Schema_And_Object_Changes_To_App_Log] WITH (STATE = ON) CREATE DATABASE AUDIT SPECIFICATION [DB_Schema_And_Object_Changes_To_App_Log_Spec] FOR SERVER AUDIT [DB_Schema_And_Object_Changes_To_App_Log] ADD (DATABASE_OBJECT_CHANGE_GROUP), ADD (SCHEMA_OBJECT_CHANGE_GROUP) WITH (STATE = ON) EXEC msdb.dbo.sp_add_alert @name=N'DB or Schema Object Change', @message_id=33205, @severity=0, @enabled=1, @delay_between_responses=0, @include_event_description_in=1, @job_id=N'00000000-0000-0000-0000-000000000000' EXEC msdb.dbo.sp_add_notification @alert_name=N'DB or Schema Object Change', @operator_name=N'DB-Notifications', @notification_method = 1