[SQL] Event Notifications in SQL Server for Tracking Changes

Event Notifications in SQL Server for Tracking Changes
http://www.mssqltips.com/tip.asp?tip=2121&home

You create a notification, SQL Server tracks the defined events
and the occurrence of that event is written to the
SSB (SQL SERVER BORKER) service and then you can retrieve this
information asynchronously from the SSB queue when needed.

Step1: Enable_Broker on a DATABASE

Step2: Create database level EVENT notification

Step3: Create SErver level EVENT notification

Step4: Verify notifications are being tracked

Step5: Retrieving (RECEIVE) records from the EVENT NOTIFICATIONS QUEUE

Permission Requirements

In order to create a database level event notification, you need to have CREATE DATABASE DDL EVENT NOTIFICATION permissions in that database. To drop it, you must be the owner of the event notification or have ALTER ANY DATABASE EVENT NOTIFICATION permission in that database.

In order to create a server level notification, you need to have CREATE DDL EVENT NOTIFICATION permissions on the server. To drop it, you must be the owner of the event notification or have ALTER ANY EVENT NOTIFICATION permission on the server.

In order to create an event notification to track SQL traces, you need to have CREATE TRACE EVENT NOTIFICATION permission on the server. To drop it, you must be the owner of the event notification or have ALTER ANY EVENT NOTIFICATION permission on the server.

In order to create a queue scoped event notification you need ALTER permission on the queue. To drop it, you must be the owner of the event notification or have ALTER permission on the queue.

Notes

You can query sys.event_notification_event_types to get the list of all the events on which you can create event notifications or you can view DDL events, DDL event groups, SQL trace events, SQL trace event groups.

To see the differences between event notifications and triggers click here and differences between event notifications and SQL traces click here.

You cannot directly alter a notification, you need to drop and re-create it.