SQL PI SQL Text Statement not showing on SQL Performance Dashboard (The server principal is not able to access the database error) (4232459)

SQL Statement not showing on SQL Performance Dashboard with the following error found in the logs:

ERROR   [DBNAME-highPriorityPool-4-[DBSS_Top_SQLs] 
com.quest.qsi.fason.core.collection.processor.InstanceProcessor - Failed to run DBSSTopSQLProcessor processor of DBSS_Top_SQLs collection.
java.lang.RuntimeException: Failed to execute collection [DBSS_Top_SQLs], reason=Failed to retrieve SQL text for SQL ID Caused by: java.lang.RuntimeException: Failed to retrieve SQL text for SQL ID ...Caused by: java.lang.RuntimeException: Failed to execute collection [DBSS_Top_SQLs], reason=[Foglight][SQLServer JDBC Driver][SQLServer]The server principal "foglightuser" is not able to access the database "realtime" under the current security context.-

Foglight monitoring user was unable to access access database ‘realtime’ due to permission.

This can particularly occur is a replica database is used with a different SID.

Users can query the sid for the database users from the original and replica copies of the database to determine if there is a different SID in each database

SELECT TOP (1000) [uid] ,[name] ,[sid] FROM [sys].[sysusers]

Grant Foglight monitoring user the required access to the monitored database

RESOLUTION 1

Grant specific access via SQL Management Studio 

1. Grant permission by navigating to Security | Logins | select user | open properties | Click 'User Mapping' on menu left side.

2. Click choose database for mapping login and for Database Role, click public.

Note that user ‘foglightuser’ must have grant database role db_datareader and db_ddladmin on database master and database msdb.

3. Restart DBSS agent.  Wait 5 min for collecting data.

RESOLUTION 2

Drop and recreate the user in the Foglight monitored database

RESOLUTION 3

Rerun the grant privileges script in the monitored database