Failed to fetch data from ANALYSIS_ITEM_FOLDERS

  • KM02416498
  • 15-Jun-2016
  • 15-Jun-2016

Summary

Users cannot have access to Releases data in the Releases module

Error

When accessing to the Releases inner tab of the Management module, an error is displayed :

image text


Error in ALM logs:

Messages:
Failed to fetch data from ANALYSIS_ITEM_FOLDERS; [Mercury][SQLServer JDBC Driver][SQLServer]Invalid object name 'ANALYSIS_ITEM_FOLDERS'. Failed SQL: /* ~~QC */ SELECT AIF_OWNER,AIF_NAME,AIF_PARENT_ID,AIF_PATH,AIF_VER_STAMP,AIF_ID,AIF_SEGMENT_ID,AIF_PUBLIC,AIF_LOGICAL_NAME,AIF_DESCRIPTION FROM ANALYSIS_ITEM_FOLDERS LEFT OUTER JOIN VF_ANALYSIS_ITEM_FOLDERS ON AIF_ID = VF_ANALYSIS_ITEM_FOLDERS.VF_AIF_ID WHERE ((AIF_ID IN (/*P*/1004)) AND (EXISTS (SELECT 1 FROM ANALYSIS_SEGMENTS WHERE AS_ID = AIF_SEGMENT_ID AND (AS_IS_PUBLIC = /*P*/'Y' OR AIF_OWNER IN (/*P*/'USER_NAME',/*P*/'__default__'))))) ORDER BY AIF_ID ASC;


Cause

Problem is caused by the view ownership at the SQL database, the database project Views ownership is not compatible with the either SQL or Windows database authentication. This is detected after upgrading/restoring an Application Lifecycle Management (ALM) project with a Microsoft SQL database server as backend,  when moving/restoring a project that was using in ALM - SQL windows authentication environment to an ALM - SQL Authentication environment, and viceversa.

Fix

For a project using MS SQL - changing views ownership to SQL authentication run :

 
Exec sp_changeobjectowner '[dbo].[V_LINK_CYCLE]','td'

Exec sp_changeobjectowner '[dbo].[V_LINK_REQ_VIA_TEST]', 'td'

Exec sp_changeobjectowner '[dbo].[V_LINK_RUN]', 'td'

Exec sp_changeobjectowner '[dbo].[V_LINK_REQ]', 'td'

Exec sp_changeobjectowner '[dbo].[V_LINK_TEST]', 'td'

Exec sp_changeobjectowner '[dbo].[V_LINK_TESTCYCL]', 'td'

Exec sp_changeobjectowner '[dbo].[VF_ANALYSIS_ITEMS]', 'td'

Exec sp_changeobjectowner '[dbo].[VF_ANALYSIS_ITEM_FOLDERS]', 'td'

Exec sp_changeobjectowner '[dbo].[VF_DASHBOARD_PAGES]', 'td'

Exec sp_changeobjectowner '[dbo].[VF_FAVORITE_HAS_SHARES]', 'td'

Exec sp_changeobjectowner '[dbo].[V_LINK_REQ_VIA_TESTCYCL]', 'td'

Exec sp_changeobjectowner '[dbo].[V_LINK_STEP]', 'td'

Exec sp_changeobjectowner '[dbo].[VF_DASHBOARD_FOLDERS]', 'td'



For a project MS SQL - changing views ownership to Windows authentcation :

Exec sp_changeobjectowner '[td].[V_LINK_CYCLE]','dbo'

Exec sp_changeobjectowner '[td].[V_LINK_REQ_VIA_TEST]', 'dbo'

Exec sp_changeobjectowner '[td].[V_LINK_RUN]', 'dbo'

Exec sp_changeobjectowner '[td].[V_LINK_REQ]', 'dbo'

Exec sp_changeobjectowner '[td].[V_LINK_TEST]', 'dbo'

Exec sp_changeobjectowner '[td].[V_LINK_TESTCYCL]', 'dbo'

Exec sp_changeobjectowner '[td].[VF_ANALYSIS_ITEMS]', 'dbo'

Exec sp_changeobjectowner '[td].[VF_ANALYSIS_ITEM_FOLDERS]', 'dbo'

Exec sp_changeobjectowner '[td].[VF_DASHBOARD_PAGES]', 'dbo'

Exec sp_changeobjectowner '[td].[VF_FAVORITE_HAS_SHARES]', 'dbo'

Exec sp_changeobjectowner '[td].[V_LINK_REQ_VIA_TESTCYCL]', 'dbo'

Exec sp_changeobjectowner '[td].[V_LINK_STEP]', 'dbo'

Exec sp_changeobjectowner '[td].[VF_DASHBOARD_FOLDERS]', 'dbo'


More details about the sp_changeobjectowner click here