Server Wide DDL Trigger Permissions Issue

Question

I am trying to implement a server wide DDL Trigger that will audit the DDL changes across the databases on the server using script very similar to the article found here

The trigger will log the DDL events into one table within one database, however I am coming up against permissions issues and some users, even myself with sysadmin privileges, receive the following error message.

Msg 297, Level 16, State 1, Procedure LogDDLEvent, Line 19
The user does not have permission to perform this action.

I've read about using the WITH EXECUTE AS clause and creating a login specifically for the purpose of executing the trigger though this doesn't appear to have resolved the issue.

Could someone advise on the correct method to implement a trigger of this kind and what permissions are required to users or to a specific login to be used in the WITH EXECUTE AS?

EDIT: Some further info

The server is running SQL Server 2008 R2 and the DDL Trigger is set to DDL_EVENTS as follows:

CREATE TRIGGER LogDDLEvent
ON ALL SERVER
FOR DDL_EVENTS
AS

DECLARE     @eventInfo XML
SET         @eventInfo = EVENTDATA()

INSERT INTO Tools.audit.DDLEvent
VALUES
(
      REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName
      , CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName
      , (SELECT   CAST(client_net_address AS VARCHAR(128))
            FROM  sys.dm_exec_connections
            WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType
      , CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand
      , @eventInfo -- DDLEventXML
)

The above script is how I originally created the trigger and this worked fine for me and some other users, though a user who had db_owner role to a particular database kept receiving the error message above.

After trying to implement the WITH EXECUTE AS I found that the trigger would not work for myself.

Answer 1

Setting the trigger to EXECUTE WITH 'sa' appeared to do the trick. Not sure if this introduces any security concerns. I tried creating a separate login with permissions only to the Tools database and DDLEvent table but non sysadmin users got errors.

CREATE TRIGGER LogDDLEvent
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR DDL_EVENTS
AS

DECLARE     @eventInfo XML
SET         @eventInfo = EVENTDATA()

INSERT INTO Tools.audit.DDLEvent
VALUES
(
      REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName
      , CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName
      , (SELECT   CAST(client_net_address AS VARCHAR(128))
            FROM  sys.dm_exec_connections
            WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType
      , CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand
      , @eventInfo -- DDLEventXML
)