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.’
“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…