Summary:

Following an upgrade, Search Select Advanced fields may not have user security settings applied to them. On searching an SSA, all records matching the given criteria (regardless of security setting) will be returned.

Symptoms:

In this specific scenario, security settigns are still applied when conducting a Find. Similar issues regarding a lack of security may be observed by browsing to the Summary screen of an affected entity, then replacing the key value in the URL with one for a record for which the user should not have access.

Cause:

The issue is caused by the method used to look up view dependencies when applying security to a screen or SSA. When a screen or SSA is loaded, data is retrieved using an appropriate view. In order to determine the appropriate security clause, a lookup is done against the SQL Server metadata which returns a list of tables or views on which the view has a dependency. This is stored on INFORMATION_SCHEMA.VIEW_TABLE_USAGE. This is a system table, the contents of which should not be edited directly.

When refreshing the CRM views metadata, the Sage CRM views are deleted and recreated. This process updates the view dependencies on INFORMATION_SCHEMA.VIEW_TABLE_USAGE in the SQL Server metadata. The CRM views are rebuilt in the order in which they appear on the Custom_Views table, form the perspective of the clustered index key.

Should a customer or partner install a component so that a view responsible for rendering a screen or SSA has a lower CuVi_ViewId than a view on which it has a dependency, the SQL Server view metadata for that view will be missing the dependencies for the views with the higher IDs. This behaviour appears to derive from SQL Server - when the dependent views are dropped, the dependencies for the higher-level view are removed, but when the views are recreated, they are not added again, despite the fact that the net change in the view structure is zero.

Resolution:

Assuming that thte issue affects the Company entity, and SSAs linking to a Company record, you can test for the presence of the issue by running the following SQL:

select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.VIEW_TABLE_USAGE WHERE view_name = 'vSummaryCompany'

If you receive fewer records than those listed below, then your issue is identical to the one described in this article:

VIEW_SCHEMAVIEW_NAMETABLE_SCHEMATABLE_NAME
dbovSummaryCompanydboAccount
dbovSummaryCompanydboUsers
dbovSummaryCompanydbovAddress
dbovSummaryCompanydbovCompanyPE
dbovSummaryCompanydbovPersonPE

In the reference case reported for this issue, the references to vCompanyPE and vCompanyPE were missing (they had a higher CuVi_ViewId on the Custom_Views table).

The issue can be worked around by removing vSummaryCompany from the Custom_Views table, then recreating it at the end of the table. Once this is done, the CRM views metadata is recreated. This can be done as follows:

1: Stop IIS.

2: In Management Studio, run the following:

declare @usingIdentities nvarchar(max)
declare @compSummaryViewScript nvarchar(max)

select @compSummaryViewScript = CuVi_ViewScript
from Custom_Views where CuVi_ViewName = N'vSummaryCompany'

select @usingIdentities =    
    case rtrim(Parm_Value)        
        when 'Y' then 'Y'        
        else 'N'    
    end
from Custom_SysParams where parm_name = N'StillGotIdentityCols'

delete from Custom_Views where CuVi_ViewName = N'vSummaryCompany'

if (@usingIdentities = 'Y') begin    
    insert into Custom_Views(CuVi_CreatedBy, CuVi_CreatedDate, CuVi_UpdatedBy, CuVi_UpdatedDate,        
        CuVi_TimeStamp, CuVi_ViewName, CuVi_Entity, CuVi_ViewScript, CuVi_Options, CuVi_CustomTableIDFK)     
    values (-1, GETDATE(), -1, GETDATE(), GETDATE(), 'vSummaryCompany', 'Company',        
        @compSummaryViewScript, 1, (            
            select Bord_TableId            
            from Custom_Tables             
            where Bord_Name = N'Company')         
        )
end
else begin    
    declare @newViewId int    
    exec @newViewId = eware_get_Identity_Id @Table_Name = 'Custom_Views'

    insert into custom_views (CuVi_ViewID, CuVi_CreatedBy, CuVi_CreatedDate, CuVi_UpdatedBy, CuVi_UpdatedDate,        
        CuVi_TimeStamp, CuVi_ViewName, CuVi_Entity, CuVi_ViewScript, CuVi_Options, CuVi_CustomTableIDFK)     
    values (@newViewId, -1, GETDATE(), -1, GETDATE(), GETDATE(), 'vSummaryCompany', 'Company',        
        @compSummaryViewScript, 1, (            
        select Bord_TableId            
        from Custom_Tables            
        where Bord_Name = N'Company')        
    ) end

drop view vSentinel
go

3: Start IIS, and log into CRM. As you log in, the views will be rebuilt.

The above script should work whether or not the Sage CRM installation has been upgraded from a version prior to 7.2.

Status:

The issue has been raised to the Development team for a fix in a future release of Sage CRM.