Error in getting transaction data for the selected module
User receives the below error message when opening Data Explorer. The user has tried to search for data in Data Explorer but no data is being returned. The user is able to navigate and utilize the other features in the Data Warehouse Manager and is able to pull in data in Report Designer. If the user is granted the "sysadmin" privilege, the user will not have this issue and can utilize the Data Warehouse Manager application regularly, however, granting each user the "sysadmin" privilege is not a feasible solution.
In the stored procedure used on the Data Explorer for retrieving transaction data, an “Tempdb” table is being used. If the user is granted "db_denydatareader" permission on this table, the above error could occur.
1. Run SQL Profiler to grab the SQL statement when the error occurs. The error should look similar to the following statement:
The SELECT permission was denied on the object 'columns', database 'mssqlsystemresource', schema 'sys'.
2. Check the user's permission and see if the "data_denydatareader" is enabled on the tempDb table.
- Go to the SQL server Security Logins select the user right click and select Properties User Mapping
- Select the tempDb database and check if the “db_denydatareader” option is checked or not. If checked, then you will have to uncheck this option.
3. You can run the below query on the server to resolve the error:
USE tempdb GO
GRANT SELECT ON sys.columns TO
- Data Warehouse Manager 4.x