SQL Server – Can WITH XMLNAMESPACES Trigger a DDL Database Trigger?

sql server

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.

Best Answer

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.

Related Question