SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server-level DDL Trigger Error


Server-level DDL Trigger Error

Author
Message
Brian Laws
Brian Laws
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 104
Greetings!

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
for drop_server_role_member
as
begin
set nocount on

begin try
insert into PrometheusAudit.dbo.Server_Audit
(
EventData
)
values
(
eventdata()
)
end try
begin catch
-- 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
end catch
end

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!
Brian Laws
Brian Laws
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 104
Wierd ... it got rid of all my indentation (spaces). Sorry about that.
peterhe
peterhe
SSC Eights!
SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)

Group: General Forum Members
Points: 890 Visits: 451

Are you using rtm? according to bol, DROP_SERVER_ROLE_MEMBER is not a server scope ddl event/event group.





Brian Laws
Brian Laws
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 104
Yes, I'm using RTM. My BOL indicates that it actually is a server-scope ddl. It's parent is DDL_SERVER_SECURITY_EVENTS. I'm ultimately trying to capture that ddl group, but I've narrowed down the issue to at least this one event. It's not listed in the table under the "Event Groups for Use with DDL Triggers" BOL topic, but it's listed as a child of DDL_SERVER_SECURITY_EVENTS if you look up that topic directly in BOL.

I've found so far that the new BOL leaves some things to be desired... Thanks!
peterhe
peterhe
SSC Eights!
SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)

Group: General Forum Members
Points: 890 Visits: 451
check this: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a3d3c1a5-6ca0-465b-b1d4-f197dd2b682d.htm in bol



Brian Laws
Brian Laws
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 104
Right, I see what you mean. However, these are individual DDL statements, not group events. ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ca518b24-6451-445c-952d-101fccef87fa.htm defines that there are two types of events: one or more particular DDL statements and a predefined group of DDL statements. The link you gave was the definition of the former. But there's still a whole bunch of DDL events which are included under the groups: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fb2a7bd0-2347-488c-bb75-734098050c7c.htm. You can look up each individually in BOL to see what is included in the groups. Unfortunately, the BOLs are very lacking in this. It only shows the XML definition and what the parent or children are.

It does, though, indicate that only some of the groups are usable in DDL triggers. But it doesn't say which ones. The chart of DDL events explicitly lists DDL_SERVER_SECURITY_EVENTS as a valid group. However, it gives me the same error. So that's when I tried it's children instead of the single group, thus narrowing it down to that one event. So I suspect that text about only some groups working is just boilerplate language for each of the events.
Brian Laws
Brian Laws
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 104
Well, I never was able to get that trigger to work. However, I found another way to do it: event notifications and the Service Broker. I created a Service Broker service which will respond to the DDL groups. I may write an article on how I put all this together, since it took me a long time to figure out. It's working pretty well, as it is auditing all DDL events (except for the create statistics one, since I don't really care about it) into a table. So far, it looks like I've got the solution.

Thanks for your help!
mobasha
mobasha
SSC Eights!
SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)

Group: General Forum Members
Points: 886 Visits: 1284
hi
am having the same problem, and am wondering have u wrote this article or not??
i need to see ur solution.
thanks

..>>..

MobashA
Carl Federl
Carl Federl
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3226 Visits: 4350
Under SQL Server 2005, the add_server_role_member and drop_server_role_member may only be used with event notification and cannot be used with DDL Triggers. The reasoning is that since only a login with sysadmin rights can affect server role membership and a sysadmin can always disable the trigger, triggers are not reliable.

However, SQL Server 2008 10.0.1075, does support triggers on the server role membership event.

SQL = Scarcely Qualifies as a Language
mobasha
mobasha
SSC Eights!
SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)SSC Eights! (886 reputation)

Group: General Forum Members
Points: 886 Visits: 1284
its earlly for my company to use 2008, so am stick with 2005.\any way im trying to use the code in the post but am trying to recored AUDIT_ADD_MEMBR_TO_DB_RLOE_EVENT but i cant get the role the user has been add to, i dont know why?

..>>..

MobashA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search