I was recently working on an issue reported by the team on the CMPivot issue.
CMPivot is a ConfigMgr tool that helps to get access to a real-time state of the devices in your environment. CMPivot tools can be used to run a query on all currently connected devices in the target collection and returns the results.
The execution of the CMPivot query is successful but it doesn't display any results in the CMPIVOT UI.
Microsoft has a nice troubleshooting guide on the CMPivot https://docs.microsoft.com/en-us/mem/configmgr/core/servers/manage/cmpivot-tsg
During the course of the investigation, I can see that the CMPivot query is successful, and data is being processed and inserted into the database but the CMPivot UI is blank.
CMPivot Query is successful.
CMPivot query results in the database.
Script output:
Next is look at the smsprov.log which reveals the following information.
*** [42000][15517][Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot execute as the database principal because the principal "smsdbuser_ReadOnly" does not exist, this type of principal cannot be impersonated, or you do not have permission. : CMPivot_RunQueryImage
From the log, it says, the account "smsdbuser_ReadOnly" doesn't exist in the SQL database when executing the CMPivot queries.
What is this account used for?
The account ‘smsdbuser_ReadOnly’ is used to read the data from the table CMPivotResult when CMPivotResult portal loads the data in the SQL database which we saw earlier.
Even though the data from the CMPivot exist in the database but to fetch the data from SQL DB to CMPivot UI, you need this account with read permissions.
This account is created by the SCCM site automatically during the installation but for some reason this is missing, which requires some investigation on the SQL side.
To fix this issue, re-create the account and granted read-only access to the database.
Read more about CMPivot
https://docs.microsoft.com/en-us/mem/configmgr/core/servers/manage/cmpivot
https://docs.microsoft.com/en-us/mem/configmgr/core/servers/manage/cmpivot-tsg