Last week I had the following error message repeating over and over again in the SQL Server log of one of my servers. It was repeating so much that the logs were growing very large, very fast:

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.

If you do a search for this error, you won't find a whole lot on it. You'll find that if you can figure out what database it is, then you should change the owner of the database.

First, check sys.databases:

Query sys.databases joined with sys.server_principals like so:

SELECT d.name AS 'Database', s.name AS 'Owner'

FROM sys.databases d
  LEFT JOIN sys.server_principals s
    ON d.owner_sid = s.sid; 

You're looking for databases owned by logins you know are no longer valid. We had a DBA recently retire and found a few that way.

Second, check to see if sys.database_principals and sys.databases match up:

We were still getting the errors and by looking at sys.databases, nothing was showing up as being wrong. However, that error means there is a mismatch with dbo matching up to a login. That means you have to take it a step further and query sys.database_principals and see how dbo matches up. For a particular DB, say Example, here's the type of query you'd run:

SELECT sp.name AS 'dbo_login', o.name AS 'sysdb_login'

FROM Example.sys.database_principals dp
  LEFT JOIN master.sys.server_principals sp
    ON dp.sid = sp.sid
  LEFT JOIN master.sys.databases d
    ON DB_ID('Example') = d.database_id
  LEFT JOIN master.sys.server_principals o
    ON d.owner_sid = o.sid
WHERE dp.name = 'dbo';

Obviously, if you wanted to run it for all DBs, you'd do something like:

EXEC sp_MSForEachDB

'SELECT ''?'' AS ''DBName'', sp.name AS ''dbo_login'', o.name AS ''sysdb_login''
FROM ?.sys.database_principals dp
  LEFT JOIN master.sys.server_principals sp
    ON dp.sid = sp.sid
  LEFT JOIN master.sys.databases d
    ON DB_ID(''?'') = d.database_id
  LEFT JOIN master.sys.server_principals o
    ON d.owner_sid = o.sid
WHERE dp.name = ''dbo'';';

By doing this, I found several databases that sys.databases said had an owner. However, when I checked it from the database's sys.database_principals, the SID didn't match up for dbo. The column I had for dbo_login came back NULL. That was a clear sign of the issue. There is also the possibility you will see a mismatch between dbo_login and sysdb_login. It appears that as long as dbo_login matches a legitimate login, the error is not generated. I found that on some DBs on one of my servers. While it's not causing a problem now, I'll be looking to correct the mismatch.

Correcting the Error:

The easiest way to correct the error is to use ALTER AUTHORIZATION on the databases which have the NULL login match for dbo. It's as simple as:

ALTER AUTHORIZATION ON DATABASE::Example TO sa;