During maintenance activity of SQL Server I noticed error 15517 message repeating on Event Viewer. Also the space of my SQL server was reducing fast. (Space reduction on SharePoint SQL DB is some time because of growing Logs, but this was not the case here). Let us fix the SQL server error 15517
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.
Error : 15517 (Event Viewer)
If you experience the space reduction then check the Log size of SQL Server. It was a shock to see the Log size on my SQL machine more than 300 GB 🙁
I was shocked to see the Log size on my SQL machine to be more than 300 GB 🙁
If you are like me and want to quickly recover the space, then execute the below command on SQL Server.Below command will close the connection to current error log file and generate the new log file after which you can delete the earlier log file.
exec sp_cycle_errorlogReason why I received Error
The error is usually received on having an issue with the dbo login of the SQL Server. To show what I mean here , I have provided the screenshot of a database where the dbo account cannot be seen.
Recently we did a Database Migration and found that the dbo of database from the original server was not copied/impersonated to new server correctly. This can be because of various reasons.
To check which databases are impacted we will run the below query
SELECT name AS DB, SUSER_SNAME(owner_sid) AS owner FROM sys.databasesIf the result under column “Owner” is showing as null, or they are not having the correct DB owner name then run the below command on all such databases.
ALTER AUTHORIZATION ON DATABASE::Example TO sa;