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.