Sagi_Gabay

Cannot find the object "EXPLORE_AND_CORRELATE" due to permissions problem

Discussion created by Sagi_Gabay Employee on Oct 17, 2011

You might encounter the following error message in Identity Manager's log file:

java.sql.SQLException: [IBM][SQLServer JDBC Driver][SQLServer]Cannot find the object "EXPLORE_AND_CORRELATE" because it does not exist or you do not have permissions.

Perhaps surprisingly this error has really nothing to do with Explore and Correlate or provisioning. When Identity Manager creates its object store it creates the EXPLORE_AND_CORRELATE table.

However, during the product's startup sequence ( in startup step 24 ) it needs to extend this table and add another column called: CONTAINERHANDLES. This table is not created with the database by default. To add this column to the table the account connecting Identity Manager to the object stores requires a 'db_owner' permission to be able to modify the database schema.

If you happen to use an account that is not a 'db_owner' then the error above will show in your startup step 24. This error resurfaces at other times as well. To fix this problem you will need to restart Identity Manager and make sure you are connecting to the object store using an account with 'db_owner' privileges. Once you have extended the table this error will not appear.

As long as this error is not fixed its only impact is not during the time it is displayed but it will have trouble to perform Explore and Correlate tasks that need to handle containers on the target endpoints. This column is required for such handle.

Below is an excerpt from start up step 24 that leads to this error when an account who's not a 'db_owner' is used. See the 'ALTER TABLE EXPLORE_AND_CORRELATE ADD "CONTAINERHANDLES" NTEXT' sql command which is what's trying to add the column which is failing on permissions as explained.

[9/28/11 10:26:29:884 EDT] 0000001c SystemOut O 10:26:29,884 INFO [ims.jdbc.JDBCSchema] Starting schema update of table EXPLORE_AND_CORRELATE
[9/28/11 10:26:29:884 EDT] 0000001c SystemOut O 10:26:29,884 DEBUG [ims.jdbc.JDBCSchema] ALTER TABLE "EXPLORE_AND_CORRELATE" ADD "CONTAINERHANDLES" NTEXT
[9/28/11 10:26:29:915 EDT] 0000001c ServiceLogger I com.ibm.ws.ffdc.IncidentStreamImpl open FFDC0009I: FFDC opened incident stream file D:\Program Files\IBM\WebSphere\AppServer\profiles\AppSrv01\logs\ffdc\server1_0000001c_11.09.28_10.26.29_0.txt
[9/28/11 10:26:29:978 EDT] 0000001c ServiceLogger I com.ibm.ws.ffdc.IncidentStreamImpl resetIncidentStream FFDC0010I: FFDC
closed incident stream file D:\Program Files\IBM\WebSphere\AppServer\profiles\AppSrv01\logs\ffdc\server1_0000001c_11.09.28_10.26.29_0.txt
[9/28/11 10:26:29:994 EDT] 0000001c ServiceLogger I com.ibm.ws.ffdc.IncidentStreamImpl open FFDC0009I: FFDC opened incident stream file D:\Program
Files\IBM\WebSphere\AppServer\profiles\AppSrv01\logs\ffdc\server1_0000001c_11.09.28_10.26.29_1.txt
[9/28/11 10:26:30:040 EDT] 0000001c ServiceLogger I com.ibm.ws.ffdc.IncidentStreamImpl resetIncidentStream FFDC0010I: FFDC closed incident stream file D:\Program
Files\IBM\WebSphere\AppServer\profiles\AppSrv01\logs\ffdc\server1_0000001c_11.09.28_10.26.29_1.txt
[9/28/11 10:26:30:087 EDT] 0000001c SystemOut O 10:26:30,087 ERROR [ims.jdbc.JDBCManagedObjectProvider] creating managed object Explore and Correlate
java.sql.SQLException: [IBM][SQLServer JDBC Driver][SQLServer]Cannot find the object "EXPLORE_AND_CORRELATE" because it does not exist or you do not have permissions.
at com.ibm.websphere.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.ibm.websphere.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.ibm.websphere.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.ibm.websphere.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.ibm.websphere.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.ibm.websphere.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
at com.ibm.websphere.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at com.ibm.websphere.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at com.ibm.websphere.jdbc.base.BaseStatement.commonExecute(Unknown Source)

This information is also available as Tech Doc: 556246 in CA's Support site:
https://support.ca.com/irj/portal/anonymous/kbtech?searchID=TEC556246&docid=556246&bypass=yes&fromscreen=kbresults

Yours,

Sagi Gabay,
CA Technologies.

Outcomes