Backup failed after upgrade with security context error

[icPeter]

Problem
We upgraded our database yesterday, from v6.4 to v7.1, and everything seemed to be in order.
However the backup seem no to be performed anymore in the Agent-Manager (SQL Server Management Studio).

The error that occur is that the user is not able to access the database “sgmaster” (etc.) under the current security context (SQLSTATE 08004) (Error 916).

Database analyses
I started SQL Server Management Studio and checked the databases (sgmaster, sgnotify, sgvault and sgvaultindex).

• All of them had the user “sa” as dbo
• The user “sgvaultuser” was found, but not found as a user under each sg-database.
Only found under general security/login/users
• ss

Tests performed
I tried to first add dbo-rights for “sgvaultuser” to all 4 sg-databases.
But no change, still the same error.

I tried to use “sa” as the user for the job in Agent-Manager, but still the same error.

Any suggestions of what can be wrong?

[Beth]

Is the user name listed in the error?

If you manually make a backup using the SQL Server Management Studio, does that still work?

What version of SQL Server are you using?

Were the jobs manually set up in SQL Server Management Studio or did another product create the backup jobs?

[icPeter]

We use SQL Server 2012 and the job was manually added for a long time ago. The job has been executing for about 6 months without problem before the upgrade.

I found the solution yesterday that seem to work for the back up job:

1. First a snapshot of the virtual server.

2. Added extra rights to the sgvaultuser.
But this had no effect, the backup still failed.

3. Entered the property dialog for the job and the "General" part.

4. Then I added multiple owners to the job.
I selected sa, sgvaultuser and all extra "IIS APPPOOL\Vault*AppPool" logins under the Owner.

After step 4 the job started to work.
I think step 2 could be skipped.
But I'm not 100% sure if any added rights had some good effect when performing step 4.

[Beth]

I think the sa user is supposed to be the user that normally runs the backups.

I'll see if I can reproduce this issue. We haven't had this reported from anyone else. So that I can, what version of SQL Server are you using and what OS is it on?