DDL Events for Use with Event Notifications

  • Article

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.