Event log filling up with Sql error

  • Windows event log filling up with

    ERROR 1 : "The activated proc '[dbo].[SqlQueryNotificationStoredProcedure-]' running on queue 'test.dbo.SqlQueryNotificationService-' output the following: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.'"

    And

    Error 2: An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

    I am using SQL Server 2017. I have tried changing owner of the database, but still receive error messages. If service broker disabled error stops, but I want to use SQL service broker feature. Is there any other method to solve this error.

  • Have you identified what query specifically it's trying to run and the structure of that query? Use Extended Events to track that down.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • What are you trying to do with notification services ?

    Chances are you can do it more easy and less intrusive on your server /  instance using Extended Events !

    With regards to notification services:

    - Is your userdatabase enabled for service broker ?

    ALTER DATABASE yourdb SET ENABLE_BROKER;

    - Is your userdatabase TRUSTWORTHY ?

    ALTER DATABASE yourdb SET TRUSTWORTHY ON;

    - Did you create the event notification in context of 'sa' ?

    We did always perfer to do it that way, so when an account gets removed, the services still work.

    /* current user get ownership of EVENT NOTIFICATION, so switch to 'sa' */
    EXEC AS LOGIN = 'sa';
    go
    -- Create the event notification at the server level for the AUDIT_LOGIN event
    CREATE EVENT NOTIFICATION Nsxxx
    ON SERVER FOR yourtopic
    TO SERVICE 'yourservicename', 'current database';

    go
    /* Switch back to original user */
    REVERT;
    GO

     

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • To add to what Johan said, I'd also check to make sure your database owner is a valid, enabled user.  I've had service broker fail to send messages because my database owner was not tied to an appropriate user.

    If it worked previously but is failing now, I'd check database owners due to the "dbo" text in the error.

    Also, if it worked previously but is failing now, what changed?  That change will probably help lead you to why it is failing.  If it has been failing for a while and you only noticed now, you may have lost the window to easily capture what changed.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • Johan Bijnens wrote:

    What are you trying to do with notification services ?

    Chances are you can do it more easy and less intrusive on your server /  instance using Extended Events !

    With regards to notification services: - Is your userdatabase enabled for service broker ?

    ALTER DATABASE yourdb SET ENABLE_BROKER;

    - Is your userdatabase TRUSTWORTHY ?

    ALTER DATABASE yourdb SET TRUSTWORTHY ON;

    - Did you create the event notification in context of 'sa' ?

    We did always perfer to do it that way, so when an account gets removed, the services still work.

    /* current user get ownership of EVENT NOTIFICATION, so switch to 'sa' */EXEC AS LOGIN = 'sa';
    go
    -- Create the event notification at the server level for the AUDIT_LOGIN event
    CREATE EVENT NOTIFICATION Nsxxx
    ON SERVER FOR yourtopic
    TO SERVICE 'yourservicename', 'current database';

    go
    /* Switch back to original user */REVERT;
    GO

    I  have enabled for service broker .

    Setting TRUSTWORTHY  option to true didn't help. I am still getting the error msg.

     

  • Mr. Brian Gale wrote:

    To add to what Johan said, I'd also check to make sure your database owner is a valid, enabled user.  I've had service broker fail to send messages because my database owner was not tied to an appropriate user.

    If it worked previously but is failing now, I'd check database owners due to the "dbo" text in the error.

    Also, if it worked previously but is failing now, what changed?  That change will probably help lead you to why it is failing.  If it has been failing for a while and you only noticed now, you may have lost the window to easily capture what changed.

    It worked previously but is failing now, Error started after Database restore.

     

  • after database restore, you need to double check and setup SSB again !

     

    check https://www.mssqltips.com/sqlservertip/5365/steps-to-restore-service-broker-enabled-database-on-new-sql-server-instance/

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply