Once your table is in place to keep track of the events, you're ready to create the trigger necessary to watch for these events. The first thing our solution looks for is any login event that occurs on the server, including any CREATE LOGIN, ALTER LOGIN, or DELETE LOGIN event. Run this script to create the trigger.

REATE TRIGGER tr_Security

ON ALL SERVER

For DDL_LOGIN_EVENTS

AS 

BEGIN

  INSERT INTO DDLTriggerTest..EventtableData
(XMLEvent, DatabaseName, SystemUser)

  VALUES (EVENDATA(), DB_NAME(), SYSTEM_USER)

END

This script creates the trigger that declares that we want to look for all DDL Login events that occur on the server. The information resulting when this trigger fires will go into the DDLTriggerTest table we created earlier. Notice that we use three functions to insert data into our table. In this context, the EVENTDATA function will gather information about logins because that's the event specified in the statement FOR DDL_LOGIN_EVENTS. The SYSTEM_USER function returns the login executing the current statement, and, as you may guess, the DB_NAME() function returns the name of the database in which the current statement is being executed. Because creating a login is a server level event, our audit trail records that the statement occurred in the master database.

To test our new trigger, we have to raise a LOGIN event on the server to be caught. The easiest way to do this is to create a new login by issuing a command such as CREATE LOGIN TestLogin WITH PASSWORD = '123456xxYYbaz.'

After running this statement, if we look in the Messages window, we see the message (1 rows(s) affected). This statement occurs because of the auditing that we have put in place. When we ran the CREATE LOGIN statement on the server, the DDL trigger was fired and a record was inserted into our EventTableData table in the DDLTriggerTest database.

What's in your XML?
To prove that the record was written to our table, you can run a SELECT query on the DDLTriggerTest table. If you're used to working with SQL Server 2000 then the data in the XMLEvent column may look strange to you. In SQL Server Management Studio (SSMS), you click on the link in the XMLEvent column to view the XML data it contains.

The XMLEvent field is populated by the EVENTDATA() function from our INSERT statement in our trigger. In this case, the EVENTDATA() function returns XML data related to the login, including the date and time of the login, the server anme, the user ID, and the machine system ID.

Only the beginning
The DDL_LOGIN_EVENT trigger is just one of over five dozen DDL events you can monitor that can help document the security of your system. Other DDL events of interest for Sarbanes-Oxley audits include creating or dropping other database objects, such as table, procedures, triggers, views, functions, etc.

In this article, I limited our discussion to server-level events. In a future article, I'll demonstrate a DDL trigger that tracks a database-level event. I'll also show you how those triggers can do more than just track events--they can actually prevent unauthorized events from occurring in your database environment that you don't want to occur.

Tim Chapman is a Database Administrator for a large company in the financial services industry. Post a comment below or write to Tim at chapman.tim@gmail.com.