0

I'm having an issue where, intermittently, a stored procedure is causing a database trigger to fire. The trigger is for CREATE_FUNCTION, CREATE_PROCEDURE, CREATE_TABLE, CREATE_VIEW, CREATE_TRIGGER but the procedure itself does not do any of those things. It's a straightforward write procedure that takes an XML parameter and inserts the values into a set of tables. The one thing that is unusual (for us) is that it's using WITH XMLNAMESPACES but it would seem weird for that to be the issue.

Disclaimer: I'm tackling this problem at once removed. I'm having to trust the original developers that they aren't also trying to create an object as part of their code above the database (they say that's the case). I'm also checking all of the tables being written into right now to make sure they don't have weird triggers that try to do something they shouldn't. So far, I've not been able to duplicate the effect in a test environment, even when impersonating a user with minimum access. This is on a SQL Server 2014 server.

This issue came to light because the point of the database trigger is to only let the "build" login create objects on the database.

So to keep my question narrow, is it possible for WITH XMLNAMESPACES to be the issue here?

UPDATE: As a follow up, I was able to get the necessary information. It looks like the error was being caused because the calling code, after calling the stored procedure in question, also called:

exec sp_describe_first_result_set N' EXEC usp_Problem_procedure @P1 ',N'@P1 xml'

And it looks like this is what's actually causing the error. It also looks like the trigger was ultimately not the only problem. Even fixing or disabling the trigger just caused a later error:

Msg 11528, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'REVERT

    --Check if SSB is enabled in this database' in procedure 'sp_send_dbmail' does not support metadata discovery.

Apparently one of the nested procs has the potential to use sp_send_dbmail (we discourage this, fwiw, as it's destroyed the Exchange server more than once).

So that's it. Once I showed the developers what was happening, they recognized that they just needed to call a method slightly differently, and that avoided the problem.

  • You're saying this happens when someone executes the trigger? What is the EVENTDATA() produced by the DDL trigger? My guess is the procedure is somehow (maybe also) getting altered or dropped/created. – Aaron Bertrand Apr 3 '18 at 17:13
  • @AaronBertrand The error happens when their C# app calls the stored procedure, but apparently only for multiple items (and can't recreate in SSMS at all). The actual error is because the database trigger deliberately throws an error if someone other than the build login is trying to create something. I'm working with them to capture EVENTDATA() info now, if we can. I'll edit my post with info hopefully here shortly. – Jason Whitish Apr 3 '18 at 17:31
  • You can change the DDL trigger to log EVENTDATA() to a table, so you don't have to change the app. You can also set up a server-side trace to see if they're sending other commands they may not know they are sending. – Aaron Bertrand Apr 3 '18 at 17:33
  • Can you post the definition of the stored procedure? – Aaron Bertrand Apr 3 '18 at 17:39
1

To the question in the title: I'm going to say NO. Something else is going on to force the DDL trigger to fire.

You should (at least temporarily) change the DDL trigger to store the EVENTDATA() output to a table so you can examine what else the app is sending when they call this stored procedure. There must be additional DDL that is being sent, such as an ALTER or DROP/CREATE, because a DDL trigger can't - by definition - be fired simply from executing a stored procedure that doesn't, itself, issue DDL commands.

You may want to set up a server-side trace, too, filtered to this application so you can see what else it might be sending to the server in addition to the call to the stored procedure, since there is a chance it is doing something they don't know. Trust but verify.

  • So, we have not been able to duplicate the error on this side after multiple attempts. I'm going to take your strategy above with EVENTDATA() and have them monitor the output table for the next week or so. – Jason Whitish Apr 3 '18 at 18:27
  • Just wanted to let you know that spending some time data-gathering helped isolate the problem. Thanks again! – Jason Whitish Apr 11 '18 at 20:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.