when you are designing a auditing table , you have to carefully consider the
events which you wanted to store in database. saving hundred 's of event is
nothing but the waste of space . if you are using sql server 2005 sp2 then
you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data 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 connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

this is simple auditing trigger i am sure it will help you . you can change
it according to your needs it capture the enough events to track down fault.

manpreet singh
DBA - SQL Server

"Noor" wrote: