Problem:
A working report suddenly stops working for a “User” in production environment with message “No data available”.
Research:
While report worked for every other user and only that particular user seems to had a problem while running the particular report. While he was able to run other reports in that same module fine.
This was a strange enough situation for us, while we checked code, security etc… at first we were not able to find the exact cause of this behavior. But we were sure it has something to do with the security that is finally applied when running the report. So, we created a test user with same roles assigned on lower environment.
During our debugging and profiling the calls to actual database saw a strange behavior.
The query generated from X++ was same for user who was able to run the report and the user who wasn’t able to run the report, but interestingly the SQL Profiler showed EXTRA parameters added to where clause of SQL that was getting executed. The extra condition caused the data to not appear – the condition was always going to result in a FALSE.
The query generated was something like below notice 1=3
SELECT ………. FROM InventTable T1 Inner Join InventDim T3 … WHERE 1=3
This cause the data to not appear for that user, AX kernel seems to validate the permissions and inject the condition to make sure that data doesn’t come from the source – making the sure that data is being security and row level security is applied though this.
Solution:
The solution to the problem was to add all used tables used in the Query for the report in the user’s privileges in AX.
HTH
CREDIT: #awesome #team #visionet #findingsolutions
Rizwan Ahmed 