This is weird! When a user runs a report, the credentials defined in the data source connection is different than the one which is being used to query against the database. This doesn't happen all the time but randomly. The credentials which is being used by the database has more permissions than the one defined in the data source connection.
While trying to fix this problem, we also realized that a particular report when run interactively doesn't display the output but my DBA confirmed that the SQL is being queried against the database. But the report works when run as a background activity.
After running the generated SQL codes in TOAD (which are different), the resulting dataset is different as well for each of the generated codes.
Anyone experienced these issues? We are on Cognos 10.2
Cognos will enter into database by the service account. Then it will execute the query against database using the userid configured under daatsource.