We're working on SMSP implementation and currently have some issues.
One main error is being seen on application servers / frontend severs.
According the error, the Agent account doesn’t have db_owner permissions.
The thing is that the agent user is the farm admin, we checked on SQL MGMT Studio and it seems to have all the permissions (on user mapping, “dbo” appears on the reported database, and on the SharePoint databases).
Also, Farm is not being seen via the Backup tab. I guess it’s related to the errors described in Health Analyzer.
Will be happy if you suggest ways to troubleshoot this.
This is the error message (for both modules “Connector” and “Storage Manager”:
Rule: db_owner Permission to Stub Database.
Module: Connector + Storage Manager
Sub-Category: SQL Permissions
Explanation: The rule checks whether or not the Agent account has the db_owner permission to the stub database. The Agent account needs this permission to create the stub database table, and also create, delete, and update stubs.
Log in to the SQL Server Instance where the stub database resides.
Navigate to security > Logins.
Locate the Agent account. If the Agent account does not exists, add it.
Double-Click the Agent account. The Login Properties pop-up window appears.
Click User Mapping in the left navigation.
In the Users mapped to this login field, select the stub database by selecting the corresponding checkbox.
In the Database role membership for field, select the db_owner checkbox.