Hi

I have tried adding AS EXECUTE to the following trigger but get the following error message

Cannot execute as the server principal because the principal "devtest" does not exist, this type of principal cannot be impersonated, or you do not have permission. This happens when I try to create a table.

I'm not too sure what permission level devtest needs to be to execute the trigger. I gave it sysadmin and got the following error.

CREATE TRIGGER [Audit_Server]

ON ALL SERVER

FOR CREATE_DATABASE , DROP_DATABASE

AS

BEGIN

EXECUTE AS LOGIN = 'devtest'

SET NOCOUNT ON;

DECLARE

@EventData XML = EVENTDATA();

DECLARE

@ip VARCHAR(32) =

(

SELECT client_net_address

FROM sys.dm_exec_connections

WHERE session_id = @@SPID

);

INSERT adminlog.dbo.dba_ddl_events

(

EventType,

EventDDL,

EventXML,

DatabaseName,

SchemaName,

ObjectName,

HostName,

IPAddress,

ProgramName,

LoginName

)

SELECT

@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),

@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),

@EventData,

DB_NAME(),

@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),

@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),

HOST_NAME(),

@ip,

PROGRAM_NAME(),

SUSER_SNAME();

END

GO