We have a Cognos BI 8.4 environment up and running for over half a year now. Today we decided to activate Metrics manager. After a few permissions problems (SQL server) we were able to init the metricstore without errormessages. After returning to the homepage of Cognos Connection we received and error. We restarted the Cognos 8 service and all content is gone!!
We do not have a recent backup so a lot of work is lost!
When i read your message it looks like you are using the same SQL user for the cognos content store and the metricstore. The cognos 8 environment was running fine until you chnaged permissions/roles in SQL server. Metric store initialization needs in this fase the Sysadmin role in SQL, because it uses stored procedures. After initialization this is not required anymore.
By giving the SQL sa permissions it creates a new schema in your database for the contentstore. When you cliked on the home button, it was pointing to the empty schema and that causes the errormessage. When you look in SQL server at the tables of your contentstore you will see tables with the same name like dbo.CMOBJNAMES and cognos.CMOBJNAMES.
execute the following steps:
stop cognos 8 service
remove the “sysadmin” role from the SQL user for your contentstore
delete the metric store database
create new SQL user for metric database with sysadmin permissions
create new database and attach the metric sql user to the database
delete all invalid schema tables
start cognos 8 service again
log into cognos connection and all your content is there again!
now create a new metric package with the new sql metric user and everything keeps on working fine!