DDL Events for Use with Event Notifications

The following table lists the DDL events that can be used to run an event notification, and the scope in which they can be run: ON DATABASE or ON SERVER. Note that each event corresponds to a Transact-SQL statement or stored procedure, with the statement syntax modified to include underscores (_) between keywords.

Note

Certain system stored procedures that perform DDL-like operations can also fire event notifications. Test your event notifications to determine their responses to system stored procedures that are run. For example, the CREATE TYPE statement and sp_addtype stored procedure will both fire an event notification that is created on a CREATE_TYPE event. However, the sp_rename stored procedure does not fire any event notifications.

Transact-SQL statement	Can be server scope (ON SERVER)	Can be database scope (ON DATABASE)
ADD_ROLE_MEMBER	X	X
ADD_SERVER_ROLE_MEMBER	X
CREATE_APPLICATION_ROLE (Applies to CREATE APPLICATION ROLE statement and sp_addapprole. If a new schema is created, this event also triggers a CREATE_SCHEMA event.)	X	X
ALTER_APPLICATION_ROLE (Applies to ALTER APPLICATION ROLE statement and sp_approlepassword.)	X	X
DROP_APPLICATION_ROLE (Applies to DROP APPLICATION ROLE statement and sp_dropapprole.)	X	X
CREATE_ASSEMBLY	X	X
ALTER_ASSEMBLY	X	X
DROP_ASSEMBLY	X	X
ALTER_AUTHORIZATION_SERVER	X
ALTER_AUTHORIZATION_DATABASE (Applies to ALTER AUTHORIZATION statement when ON DATABASE is specified, and sp_changedbowner.)	X	X
CREATE_CERTIFICATE	X	X
ALTER_CERTIFICATE	X	X
DROP_CERTIFICATE	X	X
CREATE_CONTRACT	X	X
DROP_CONTRACT	X	X
CREATE DATABASE	X
ALTER DATABASE	X	X
DROP DATABASE	X
GRANT_DATABASE	X	X
DENY_DATABASE	X	X
REVOKE_DATABASE	X	X
DROP_ROLE_MEMBER	X	X
DROP_SERVER_ROLE_MEMBER	X
CREATE_ENDPOINT	X
ALTER_ENDPOINT	X	X
DROP_ENDPOINT	X
CREATE_EVENT_NOTIFICATION	X	X
DROP_EVENT_NOTIFICATION	X	X
CREATE_FUNCTION	X	X
ALTER_FUNCTION	X	X
DROP_FUNCTION	X	X
CREATE_INDEX	X	X
ALTER_INDEX	X	X
DROP_INDEX	X	X
CREATE_LOGIN (Applies to CREATE LOGIN statement, sp_addlogin, sp_grantlogin, xp_grantlogin, and sp_denylogin when used on a nonexistent login that must be implicitly created.)	X
ALTER_LOGIN (Applies to ALTER LOGIN statement, sp_defaultdb, sp_defaultlanguage, sp_password, and sp_change_users_login when Auto_Fix is specified.)	X
DROP_LOGIN (Applies to DROP LOGIN statement, sp_droplogin, sp_revokelogin, and xp_revokelogin.)	X
CREATE_MESSAGE_TYPE	X	X
ALTER_MESSAGE_TYPE	X	X
DROP_MESSAGE_TYPE	X	X
CREATE_PARTITION_FUNCTION	X	X
ALTER_PARTITION_FUNCTION	X	X
DROP_PARTITION_FUNCTION	X	X
CREATE_PARTITION_SCHEME	X	X
ALTER_PARTITION_SCHEME	X	X
DROP_PARTITION_SCHEME	X	X
CREATE_PROCEDURE	X	X
ALTER_PROCEDURE	X	X
DROP_PROCEDURE	X	X
CREATE_QUEUE	X	X
ALTER_QUEUE	X	X
DROP_QUEUE	X	X
CREATE_REMOTE_SERVICE_BINDING	X	X
ALTER_REMOTE_SERVICE_BINDING	X	X
DROP_REMOTE_SERVICE_BINDING	X	X
CREATE_ROLE (Applies to CREATE ROLE statement, sp_addrole, and sp_addgroup.)	X	X
ALTER_ROLE	X	X
DROP_ROLE (Applies to DROP ROLE statement, sp_droprole, and sp_dropgroup.)	X	X
CREATE_ROUTE	X	X
ALTER_ROUTE	X	X
DROP_ROUTE	X	X
CREATE_SCHEMA (Applies to CREATE SCHEMA statement, sp_addrole, sp_adduser, sp_addgroup, and sp_grantdbaccess.)	X	X
ALTER_SCHEMA (Applies to ALTER SCHEMA statement and sp_changeobjectowner.)	X	X
DROP_SCHEMA	X	X
GRANT_SERVER	X
DENY_SERVER	X
REVOKE_SERVER	X
CREATE_SERVICE	X	X
ALTER_SERVICE	X	X
DROP_SERVICE	X	X
CREATE_STATISTICS	X	X
UPDATE_STATISTICS	X	X
DROP STATISTICS	X	X
CREATE_SYNONYM	X	X
DROP_SYNONYM	X	X
CREATE_TABLE	X	X
ALTER_TABLE	X	X
DROP_TABLE	X	X
CREATE_TRIGGER	X	X
ALTER_TRIGGER	X	X
DROP_TRIGGER	X	X
CREATE_TYPE (Applies to CREATE TYPE statement and sp_addtype.)	X	X
DROP_TYPE (Applies to DROP TYPE statement and sp_droptype.)	X	X
CREATE_USER (Applies to CREATE USER statement, sp_adduser, and sp_grantdbaccess.)	X	X
ALTER_USER	X	X
DROP_USER (Applies to DROP USER statement, sp_dropuser, and sp_revokedbaccess.)	X	X
CREATE_VIEW	X	X
ALTER_VIEW	X	X
DROP_VIEW	X	X
CREATE_XML_INDEX	X	X
CREATE_XML_SCHEMA_COLLECTION	X	X
ALTER_XML_SCHEMA_COLLECTION	X	X
DROP_XML_SCHEMA_COLLECTION	X	X

See Also

Concepts

Designing Event Notifications

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	New content: Added a note that recommends testing event notifications to determine their responses to stored procedures that are executed. Also added the names of those stored procedures to their corresponding events. Added event ALTER_ENDPOINT.
5 December 2005	New content: Added events ADD_ROLE_MEMBER, ADD_SERVER_ROLE_MEMBER, DROP_ROLE_MEMBER, and DROP_SERVER_ROLE_MEMBER.