I've been working on a SQL 2005 project, and have really been excited about the new DDL triggers. I managed to implement some database-level DDL triggers successfully. However, I keep getting an error when I try to create a server-level DDL trigger. For several of the event groups, I keep getting the following error: Msg 1082, Level 15, State 1, Procedure tr_Server_Audit_Security, Line 44 "DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger registration. I can't figure this one out for the life of me.
Here's a sample of the trigger I'm trying to create (BTW, this code works for database triggers, with some slight mods of course):
create trigger tr_Server_Audit_Security on all server
set nocount on
insert into PrometheusAudit.dbo.Server_Audit
-- Raise the error to the user and record in the PrometheusErrors.dbo.Errors table
declare @error_message nvarchar(4000), @error_number int, @error_state int, @error_severity int, @error_procedure nvarchar(126), @error_line int
select @error_number = error_number(), @error_state = error_state(), @error_severity = error_severity(), @error_procedure = error_procedure(), @error_line = error_line()
set @error_message = left('Error auditing server security transaction. Error Message: ' + error_message(), 4000)
raiserror(70004, @error_severity, @error_state, @error_message)
exec PrometheusErrors.dbo.usp_WriteError @error_message, @error_number, @error_state, @error_severity, @error_procedure, @error_line
I get the same error if I only have one line in the trigger body. I've only been able to get this to work using the following event groups: ddl_login_events, ddl_gdr_server_events, ddl_authorization_server_events. My goal is to use the single ddl_server_security_events event group.
Does anyone have any clue as to what's going on here? Thanks a ton!