ok i created this complete example by merging some of your stuff into my known, working trigger example.
it's working well for me, but has a couple of duplicate columns from what you were capturing; additionally, if you are using 2008 and above, you can see i'm capturing the IP addresses and stuff related to the connection(not available in 2005)
also note i gave the explicit path to the audit table, which yours would not , and could potentially fail if the user who is adding a user does nto have a default database set to master.
use master;
use master;
CREATE TABLE [master].[dbo].[DDLEVENTLOG] (
[SERVERNAME] VARCHAR(128) NULL,
[DATABASENAME] VARCHAR(128) NULL,
[EVENTDATE] DATETIME NULL DEFAULT (getdate()),
[SPID] INT NULL,
[LOGINNAME] VARCHAR(128) NULL,
[USERNAME] VARCHAR(128) NULL,
[SYSTEMUSER] VARCHAR(128) NULL,
[CURRENTUSER] VARCHAR(128) NULL,
[ORIGINALUSER] VARCHAR(128) NULL,
[HOSTNAME] VARCHAR(128) NULL,
[APPLICATIONNAME] VARCHAR(128) NULL,
[PROGRAMNAME] VARCHAR(128) NULL,
[NET_TRANSPORT] VARCHAR(128) NULL,
[PROTOCOL_TYPE] VARCHAR(128) NULL,
[AUTH_SCHEME] VARCHAR(128) NULL,
[SERVER_NET_ADDRESS] VARCHAR(128) NULL,
[SERVER_TCP_PORT] VARCHAR(128) NULL,
[CLIENT_IP_ADDRESS] VARCHAR(128) NULL,
[PHYSICAL_NET_TRANSPORT] VARCHAR(128) NULL,
[EVENTTYPE] VARCHAR(128) NULL,
[SCHEMANAME] VARCHAR(128) NULL,
[OBJECTNAME] VARCHAR(128) NULL,
[OBJECTTYPE] VARCHAR(128) NULL,
[EVENTDATA] XML NULL,
[COMMANDTEXT] VARCHAR(max) NULL);
--if everyone does not have access to this table, all non-sysadmins will fail in the login trigger.
GRANT INSERT ON [dbo].[DDLEVENTLOG] TO PUBLIC;
GO
CREATE TRIGGER [trg_audit]
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
, DDL_GDR_SERVER_EVENTS
, DDL_AUTHORIZATION_SERVER_EVENTS
, CREATE_DATABASE
, DROP_DATABASE
, ALTER_DATABASE
AS
DECLARE @xmlEventData XML
SET @xmlEventData = eventdata()
INSERT INTO [master].[dbo].[DDLEVENTLOG]
(
SERVERNAME,
DATABASENAME,
EVENTDATE,
SPID,
LOGINNAME,
USERNAME,
SYSTEMUSER,
CURRENTUSER,
ORIGINALUSER,
HOSTNAME,
APPLICATIONNAME,
PROGRAMNAME,
NET_TRANSPORT,
PROTOCOL_TYPE,
AUTH_SCHEME,
SERVER_NET_ADDRESS,
SERVER_TCP_PORT,
CLIENT_IP_ADDRESS,
PHYSICAL_NET_TRANSPORT,
EVENTTYPE,
SCHEMANAME,
OBJECTNAME,
OBJECTTYPE,
[EVENTDATA],
COMMANDTEXT
)
SELECT
CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')) AS SERVERNAME,
CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')) AS DATABASENAME,
GETDATE() AS EVENTDATE,
CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/SPID)')) AS SPID,
CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/LoginName)')) AS LOGINNAME,
CONVERT(VARCHAR(100), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')) AS USERNAME,
SUSER_SNAME() AS SYSTEMUSER,
CURRENT_USER AS CURRENTUSER,
ORIGINAL_LOGIN() AS ORIGINALUSER,
HOST_NAME() AS HOSTNAME,
APP_NAME() AS APPLICATIONNAME,
PROGRAM_NAME() AS PROGRAMNAME,
CONVERT(VARCHAR(128),ConnectionProperty('net_transport')) AS NET_TRANSPORT,
CONVERT(VARCHAR(128),ConnectionProperty('protocol_type')) AS PROTOCOL_TYPE,
CONVERT(VARCHAR(128),ConnectionProperty('auth_scheme')) AS AUTH_SCHEME,
CONVERT(VARCHAR(128),ConnectionProperty('local_net_address')) AS SERVER_NET_ADDRESS,
CONVERT(VARCHAR(128),ConnectionProperty('local_tcp_port')) AS SERVER_TCP_PORT,
CONVERT(VARCHAR(128),ConnectionProperty('client_net_address')) AS CLIENT_IP_ADDRESS,
CONVERT(VARCHAR(128),ConnectionProperty('physical_net_transport')) AS PHYSICAL_NET_TRANSPORT,
CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')) AS EVENTTYPE,
CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/SchemaName)')) AS SCHEMANAME,
CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')) AS OBJECTNAME,
CONVERT(VARCHAR(128), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')) AS OBJECTTYPE,
@xmlEventData AS [EVENTDATA],
CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) AS COMMANDTEXT
GO
ENABLE TRIGGER [trg_audit] ON ALL SERVER
/*
Cleanup:
DISABLE TRIGGER [trg_audit] ON ALL SERVER
DROP TRIGGER [trg_audit] ON ALL SERVER
DROP TABLE [master].[dbo].[DDLEVENTLOG]
*/
Lowell