SQL Statement not showing on SQL Performance Dashboard with the following error found in the logs:
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 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.
Drop and recreate the user in the Foglight monitored database
Rerun the grant privileges script in the monitored database