Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

DDL Events for Use with Event Notifications

SQL Server 2005

Updated: 14 April 2006

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.

ms189540.note(en-US,SQL.90).gifNote:
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

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.

Community Additions

ADD
Show:
© 2015 Microsoft