none
user SA perform activities with SQL Server Broker

    Question

  • Hello Friends:

    Actually i have Windows server 2008 R2 standard SP1 and SQL Server 2008 R2 SP3 standard and i have configured Service Broker and actually i have a tool called GFI Events Manager that trace different queries and i have configured this tool for determinate me "suspect" queries like "drops", "creates".  I have noted that exists this event:

    DROP PROCEDURE [SqlQueryNotificationStoredProcedure-99d491c5-876a-4f7b-bdcb-c424b1071cda]; 

    LOGIN :SA 

    I was checking this procedure and i found that this query have relation with Service Broker; however i don't know why appears that this action is executed by "SA" user.  This procedure isn't executed by any application.  So my questions are:

    1) Why appears this drop? if any application in our organization execute it?  Is it normal?

    2) Why it is being executed by user "sa"? is normal too? and why?

    Please help me with this 

    Kind Regards


    • Edited by Aleds Tuesday, July 10, 2018 5:15 PM
    Tuesday, July 10, 2018 5:14 PM

All replies

  • Do you see anything/unusual error messages in the SQL error logs? If so, please post here.

    This could be due to the ADO.Net SqlDependency component. Also, do you see this (i.e. DROP PROCEDURE) message frequently occurring such as every 5 seconds etc.?

    It seems the SqlDependency cleanup procedure is attempting to drop the procedure which happens by default. 

    The following article talks about a very similar error. If you do see error messages in SQL error logs, I would cross-check with the errors discussed in the article below and fix them (the potential fixes are also discussed there).

    As for "SA", it could be tied to the Database Owner. See if your DB owner is SA.

    http://rusanu.com/2007/11/10/when-it-rains-it-pours/


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, July 10, 2018 5:59 PM
  • Hello,

    As Mohsin wrote, this belongs to Query Notifications in SQL Server and internal processes are performed with the "sa" account, even if you set SQL Server auth to "Windows only".


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, July 14, 2018 2:28 AM
    Moderator