none
trace user action and audit that RRS feed

  • 問題

  • Our database is a mission-critical system. All create,alter ... sholud be logged and traced.
    SQL Profiler is too larger ,slow and inconvenience to query.
    Any ideas anyone?
    Thank you for any assistance.
    2006年11月8日 下午 11:53

解答

  • Hi.
    In SQL Server 2005, you can DDL triggers or Event Notifications.

    DDL triggers, like regular triggers, fire stored procedures in response to an event. However, unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP. Certain system stored procedures that perform DDL-like operations can also fire DDL triggers.
    DDL triggers can be used for administrative tasks such as auditing and regulating database operations.

    Event notifications execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information about these events to a Service Broker service.
    Event notifications can be used to do the following:
    Log and review changes or activity occurring on the database.
    Perform an action in response to an event in an asynchronous instead of synchronous manner.
    Event notifications can offer a programming alternative to DDL triggers and SQL Trace.
    Event notifications run asynchronously, outside the scope of a transaction. Therefore, unlike DDL triggers, event notifications can be used inside a database application to respond to events without using any resources defined by the immediate transaction.

    Please Refer below URL:
    Understanding DDL Triggers
    http://msdn2.microsoft.com/en-us/library/ms175941.aspx
    Understanding Event Notifications
    http://msdn2.microsoft.com/en-us/library/ms190427.aspx

    hope this helps..

    Regard,
    Derrick Chen

    2006年11月9日 上午 01:30