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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy