1

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:

DATABASE_OBJECT_CHANGE_GROUP
SCHEMA_OBJECT_CHANGE_GROUP

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.