Accessing data from another web application throws an SQLInvalidException

Question

The scenario is:

1. A MySiteHost site collection (example name: MY SITE). Web application's application pool account is spmysite.
2. A TeamSite site collection (example name: TEAM SITE). Web application's application pool account is spteamsite.
3. A webpart at MY SITE is retrieving data from TEAM SITE; specifically it's trying to retrieve SPGroups.
4. Hitting the following SQL Exception during data retrieval that MY SITE's application pool account could not login to TEAM SITE's content DB.

Tried:

1. Changing MY SITE's application pool account to be the same as TEAM SITE's (spteamsite). Data retrieval successful! But we have a requirement that different web applications must have different application pool accounts. :(
2. Changing MY SITE's application pool account to the farm account (ex: spfarm). Data retrieval successful! But we have a requirement that we should not be using the farm account.
3. In TEAM SITE's content DB, granted SPDataAccess or DBOwner role to spmysite. Unsuccessful. Side effect is in accessing MY SITE, it's hitting the "This website is not shared to you" error page (Cannot make sense).

We're pretty sure it's more of a configuration problem.

Appreciate your time and help.

Answer 1

The right way to grant access is to use the SPWebApplication.GrantAccessToProcessIdentity method. It sets up the database permissions for you, but remember to run it again if you add content databases.

You can do this with the following PowerShell:

$webApp = Get-SPWebApplication TEAM_SITE_URL
$webApp.GrantAccessToProcessIdentity("domain\username of MY SITE pool account")