Infrastructure at your Service

Stéphane Haby

SQL Server: Cannot execute as the database principal “dbo” does not exist

By a customer this month, I see a very big big ErrorLog file (more than 1,5GB).
When I open the file, every 5 seconds, the following message is added:

The activated proc ‘[dbo].[IdentityServerNotificationCleanup]’ running on queue ‘<database>.dbo.IdentityServerNotificationsQueue’ 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.’

My first reflex was to open SSMS and right-click on the database to see the properties but I receive the this error message:

“Property Owner is not available for Database <database>” not allows me to open the properties.

I open a query and ask to show all owner_sid with the view sys.databases:

SELECT name, owner_sid FROM sys.databases

The result give me 2 type of sid:

  • 0x01 who is the SQL Login SA
  • 0x0105…. where is windows authentication

To have more information, I use SUSER_SNAME to have the name associad with the owner_sid:

SELECT name, owner_sid, SUSER_SNAME(owner_sid) as owner_name FROM sys.databases

As you can see in the result, some owner_name are NULL for a lot of database.

To avoid this situation, I will force the ‘sa’ account to be the owner.

I generate for each line, the command using sp_changedbowner and a clause where SNAME(owner_sid) is null in my query:

SELECT name, owner_sid,SUSER_SNAME(owner_sid) as owner_name,N'use ['+name+N'];EXEC sp_changedbowner ''sa'';' as Script_change_owner FROM sys.databases where SUSER_SNAME(owner_sid) is null

Applying the script generated and all databases have again a valid owner.

Finally, I don’t have anymore the message in the errorlog every 5 seconds and can correctly read the file after recycling it.
And of course, now I can open the properties through SSMS without error message.

I hope this blog and script can help you if you have the same error message…

 

One Comment

  • Craig says:

    Thanks Stéphane, I was deleting a distributor for a transactional rep set and dbo message does-not-exist message popped up. Followed your idea and did a search for a missing owner, and that was the source of my problem! Thanks again…

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Haby
Stéphane Haby

Delivery Manager and Senior Consultant