SQL Server 2005 continues to support auditing successful and failed logins as previous releases did. In SQL Server Management Studio, you right-click the server instance in Object Explorer, select Properties to launch the Server Properties dialog, and on this dialog, you use the Security page to turn on or off auditing of successful or failed logins.
In addition to login auditing of the database engine, SQL Server 2005 now also supports auditing of Analysis Services. The Security Audit event class in the SQL Profiler tool can be used to audit login and logout actions. To see this in action, you can launch Profiler, connect to an Analysis Services instance, and select the Security Audit event class.
When you connect to a SQL Server 2005 instance by using Profiler, you see a lot of new events under the Security Audit class. Examples of these new security audit events include Audit Schema Object GDR Event, Audit Database Object Take Ownership Event, Audit Server Principal Impersonation Event, Audit Broker Conversation, Audit Broker Login, and so on.
Using triggers is one of the ways in which developers implement a custom auditing solution to track DML events, such as INSERT, UPDATE, and DELETE. SQL Server 2005 extends this model, and now you can use the new DDL triggers or event notification features to implement custom auditing solutions to track DDL events, such as altering a table, dropping a view, and so on. DDL triggers and event notifications are discussed in great detail in Chapter 6, "Transact-SQL Enhancements."