Resolving SQL PI SQL Text Visibility Issues Caused by Login Mapping Errors (4232459)

In some environments, SQL statements are not displayed on the SQL Performance Dashboard. The following error may appear in the Foglight Management Server 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.-

This issue occurs when the Foglight monitoring login (foglightuser) cannot access the monitored database due to a mismatch between the login and the database user.

This is commonly seen when a database is restored from another SQL Server instance. Even if the same login name exists on both instances, the Security Identifier (SID) for the login may differ. SQL Server uses the SID—not the login name—to associate a login with a database user.

As a result, the restored database contains a user (foglightuser) whose SID does not match the SID of the login on the new instance. This causes access errors and prevents SQL PI from retrieving SQL text.

You can verify the SID mismatch by running the following query in both the original and restored databases:

USE [YourDatabaseName];

SELECT [uid], [name], [sid] FROM sys.sysusers WHERE name = 'foglightuser';

Resolution 1: Grant Access via SQL Server Management Studio (SSMS)

1. Open SSMS and navigate to:
   • Security > Logins > foglightuser
2. Right-click and select Properties.
3. Go to User Mapping.
4. Check the box for the affected database.
5. Assign the following roles:
   • public
   • db_datareader
   • db_ddladmin (if required)
6. Click OK to apply changes.
7. Restart the DBSS agent in Foglight and wait 5 minutes for data collection.

Resolution 2: Drop and Recreate the Database User

If the SID mismatch is confirmed, drop and recreate the user in the affected database:

USE [YourDatabaseName];
DROP USER [foglightuser];
CREATE USER [foglightuser] FOR LOGIN [foglightuser];

This remaps the database user to the correct login SID on the current instance.

Resolution 3: Re-run the Grant Privileges Script

If you have a standard script for granting permissions to the foglightuser, re-run it on the affected (restored) database to ensure proper access.

• This issue is common in environments using replication, log shipping, or manual database restores across instances.
• More information about database role db_datareader and db_ddladmin  can be found in the Foglight for Database Deployment Guide.