We are prepping to launch the Reporting features within the next couple weeks. Per the documentation, I have created a new ReportingDB, along with a separate service account login to read the CommunityDB and write to the ReportingDB.
I now have two service accounts:
- CommunitySA - Has full db_datawriter, db_ddladmin, db_datareader etc access to the CommunityDB.
- ReportingSA - Has db_owner, db_datawriter, db_ddladmin, db_datareader permission to the ReportingDB as well as db_datareader to the CommunityDB
The ReportingSA will be used for the Reporting ETL and the CommunitySA is used as the SA for the Web servers as well as the Jobs Server.
Here is my question:
When we set up the Web server, we specify to use the CommunitySA in our ApplicationPool. We also set the ReportingDB connection strings in our Web connectionstring.config file. However, per the documentation, CommunitySA does not have any permissions inside the ReportingDB, so what is the purpose of defining the connection string to your reporting database? Is the CommunitySA supposed to have some level of access to the ReportingDB?
