messages aren't getting through after backup / restore on a different server...

Hot Topic

Filtering
Cannot connect to SQL Server
linked server: Error 17: SQL Server does not exist or access denied.
Using Yukon to get Objec
Looping through rows in flat file
Attempt to fetch logical page error
SQL server configuration manager can to other instances of SQL server
SSIS LOGGING
Wait for Recieve Fails to recieve message when called from .Net sqlClient
Report Builder logon failed

SQL Server

Problem with User Defined Function
readxml doesnt work :(
Unable to retrieve data from AdventureWoks
"product level is insufficient" message during flat file import
Enterprise Manager: Auto Parenthesis
Cube Process Error : The version of the linked Measure group xxxxx has changed.
Extending BI studio
I want write a Trigger that make a 3nd table that contain all the record
String to Date Conversions
Shared data source

messages aren't getting through after backup / restore on a different server...

after restoring a database and regenerating keys, I'm trying to queue messages, and they're all hanging out in sys.transmission_queue with the following message:


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 can't find any documentation or blog info on this error... Help!

thanks!

Answer this question

messages aren't getting through after backup / restore on a different server...

  • NinjaCodeMonkey

    Have you set your database to trustworty

    ALTER DATABASE db_name
    SET TRUSTWORTHY ON

    Also, maje sure you have a master key in both databases.

    CREATE DATABASE MASTER KEY
    ENCRYPTION BY PASSWORD = 'somePassW0rd1'

    Please let us know if this helps

    Niels

  • SMFX

    Which server principal (i.e. login) owns the restored database Has anything changed with the SQL instance or Windows users since backup was taken For example you are trying to restore on a machine not connected to a domain and the server principal owning the database was a domain user

    Later,
    Rushi

  • Caddre

    Probably the dbo of the database is the original login that created the database, on the original server, and that login cannot be impersonated on the new server (e.g. the login is the Windows login corresponding to the original server Administrator account).
    Use ALTER AUTHORIZATION ON DATABASE <dbname> TO <loginname> to change the owner of the database to a new login (e.g. [sa]), it will change the dbo to map to a login than is OK to be impersonated.

    HTH,
    ~ Remus

  • pdns

    thanks Remus -- that solved the problem.

  • Edgar

    Rushi -- a domain administrator owns both databases.
    Both servers are connected to the domain, and all services are also run by the domain administrator.

  • Ron Kaps

    hi Niels.

    Nope, the SET TRUSTWORTHY ON on the database did not work.

    It would probably be helpful if I mentioned that the backup was taken on the April CTP server, and the restore on the September version...

    also - both the initiator and target queues live in the same database...
  • messages aren't getting through after backup / restore on a different server...
Answer this question