• 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!