Sql-server – Why is CDC capture failing due to a missing dbo principal

I had the same exact problem - I had a new user which wanted to add jobs. I added this user to msdb db and added him to sqlAgentOperatorRole and db_datareader, db_datawriter. Still, when trying to add a new job, the user was getting the same error:

Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft Server, Error: 15517)

I traced it down and it looks like when trying to add a new job (or doing many other things...) the following query is run:

exec master.dbo.sp_Msdbuserpriv 'serv'

So, obviously I had to add the new user to Master as well:

use master
create user [youNewUser] for login [yourNewUserLogin]
alter user [youNewUser] with default_schema=[dbo]

And now it works like charm...

Create a domain user that has read/write privileges to both the network share as well as NTFS read/write permissions on the folder.

In Configuration Manager, set the SQL Agent user to this domain user. SQL Agent is the account that will execute the backup as a task. The user does not have to be a domain admin, as long as it has the NTFS permissions it needs. You can also make it an Active Directory Managed Service Account, but that's probably down the road from now.

You should be able to keep the SQL Server Service user as the local NETWORK SERVICE account, though if you were to run this script from SSMS, it will probably still fail. You'll want to schedule a one-time backup job from within the SQL Server Agent jobs in SSMS to get a real test of the configuration.

Another option altogether would be to backup to a local drive and have a Windows scheduled task move the file to the network share. This would prevent the backup from failing if it couldn't reach the other machine for any reason.

EDIT :

For those just finding this, you should know that on any machine when it uses its local SYSTEM or NETWORK SERVICE account, other systems see it as the machine name with a dollar sign ($) after it.

E.g. :

MyDomain\Computer1
User: NETWORK SERVICE

MyDomain\Computer2
User: MyDomain\Computer1$

It even works on machines that are not on a domain at all (so long as they can reach each other on the network).