November 9, 2009 at 5:08 am
I found an interesting post on Microsoft Connect on the subject: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=509242 .
Apparently there's no way to know (even with the Profiler) when/how/who... disable a trigger with the new statement DISABLE TRIGGER [...]. That's really annoying when you need to audit for security purpose your production databases on which a trigger is set to limit or trace user's action for example.
Can some DBA masters post their opinion on the subject? If you have a workaround, it is welcome!
--
Philippe RUELLO
Database Project Manager
November 9, 2009 at 2:32 pm
You can see the DISABLE TRIGGER command using SQL Trace (Profiler). It comes through as TextData in the SQL:Batch* and SQL:Stmt* events, it does not have a specific event.
Now as far as this event not being available for auditing/blocking in DDL triggers, well, that is another issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 9, 2009 at 3:06 pm
And since this is a SQL 2008 thread if you are running a SQL Audit on the audit group "SERVER_OBJECT_CHANGE_GROUP".
SQL 2008 BOL Oct 2009: http://msdn.microsoft.com/en-us/library/cc280663.aspx
BTW SQL Audit is cool..
CEWII
November 10, 2009 at 1:53 am
Hello Jack and Helliott,
Thanks for the feedback.
Jack I see the trace in the profiler (with no event) with object name as "ENABLE DISABLE TRIGGER". Thanks for the info. But it's really a big overhead to let a profiler on a production server to simply audit this kind of statements.
Helliott, I saw the SQL Audit functionality but it's only available in the enterprise edition. As we only have the standard edition here, it's not helpful for us by now.
Kind regards,
--
Philippe RUELLO
Database Project Manager
November 10, 2009 at 5:41 am
Actually if you use a server-side trace with only the sql:stmtcompleted event filtering on TextData you probably wouldn't even realize the trace is running. Are you aware that with 2005/2008 there is a trace running by default, called the Default Trace, that collects data for 34 (2005) or 35 (2008) events?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2009 at 8:34 am
pruello-1014825 (11/10/2009)
Elliott, I saw the SQL Audit functionality but it's only available in the enterprise edition. As we only have the standard edition here, it's not helpful for us by now.
Alas I forgot about that part..
CEWII
November 10, 2009 at 11:52 am
Jack Corbett (11/10/2009)
Actually if you use a server-side trace with only the sql:stmtcompleted event filtering on TextData you probably wouldn't even realize the trace is running. Are you aware that with 2005/2008 there is a trace running by default, called the Default Trace, that collects data for 34 (2005) or 35 (2008) events?
Thanks Jack for suggesting that solution. I'll have a look on Friday on the way I can implement that.
--
Philippe RUELLO
Database Project Manager
November 13, 2009 at 3:45 am
Jack Corbett (11/10/2009)
Actually if you use a server-side trace with only the sql:stmtcompleted event filtering on TextData you probably wouldn't even realize the trace is running. Are you aware that with 2005/2008 there is a trace running by default, called the Default Trace, that collects data for 34 (2005) or 35 (2008) events?
Thanks ahead for the info Jack. Default trace cannot be modified but I created mine with TextData filtered on '%DISABLE%TRIGGER%' having a transactionID as there's no peculiar event for this kind of statement.
Works fine. Best regards,
--
Philippe RUELLO
Database Project Manager
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply