When running reports on DSM Reporter, generally when reports have an old creation date (new reports work well), the following error is received:
"Encountered an improper argument."
Checking DSM Reporter logs the following ERROR is visible:
|REPORTER |DbImpl_Ado |DbImpl_Ado.cpp |002311|NOTIFY | evalSQLInfo sqlstate: 42000 native error: 229 0xe5
|REPORTER |DbImplAdo |DbImpl_Ado.cpp |002358|NOTIFY | evalSQLInfo class: 14 state: 5 server: DBITCM
|REPORTER |CDb |DbImpl_Ado.cpp |002644|NOTIFY | Error Message: ADO Version 6.1 - COM Error: ErrorCode:-2147217911,WordErrorCode:3081, IDispatch error #3081, The SELECT permission was denied on the object 'R000006', database 'mdb', schema 'dbo'., Microsoft SQL Server Native Client 10.0, (null)
|REPORTER |Reporter | |000000|WARNING| SQL failed: SELECT * FROM R000006 ORDER BY NAME ASC, IDX ASC
If the above query (SELECT * FROM R000006) is executed against the MDB the same message is received:
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'R000006', database 'mdb', schema 'dbo'.
DSM Reporter – All Versions
Every report result is stored in a temporary table on the MDB, which has the format “R00****”. The reason of the error is that those tables were created under 'dbo' schema rather than 'ca_itrm'. In order to verify this, in the database server is possible to check the prefix of the table, it will be something like: “dbo.R00****”.
This is happening because old reports were created under DBO schema and hence ‘ca_itrm’ user doesn't have permissions to view those reports.
There are two ways to solve the problem, which are:
1. For all those temp tables prefixed with 'dbo', add permissions for ‘ca_itrm’ user.
2. Re-run the reports so that new results will be generated.
To assign the needed permissions to the tables, open the properties section of “dbo.R00***” temp table and assign SELECT, UPDATE, INSERT and DELETE to ‘ca_itrm_group’ and ‘ca_itrm_group_ams’.