09-14-2013 10:30 AM
Resolved an odd issue, so I'm posting for posterity. Logged Into the web client as a standard user, although we could navigate to all Contacts, the Contacts tab under Account Detail returned 0 results. Same for opportunities, sales orders, and other child entities of accounts.
The integrity checker showed nothing out of place in the DB.
Using the SLXProfiler (which BTW didn't work until we had installed all updates up through SNC 09) we found the SQL call for the Contacts tab. We determined that the SECRIGHTS joins caused empty results. Something appeared wrong with SECRIGHTS.
As I further analyzed the SQL call, I saw that it was using the ACCOUNT_ view. This view is defined as all ACCOUNT fields, joined to SECRIGHTS on SECCODEID in order to include an ACCESSID_ field. SELECT DISTINCT ACCESSID_ FROM ACCCOUNT_ showed that all ACCESSID_ fields in the view were null!
In SQL Server Management Studio, I confirmed the ACCOUNT_ view had somehow been mangled. At the end of the view's field definitions there were some custom fields. It looked like this:
[...], A1.CUSTOM1 as ACCESSID_, A1.CUSTOM2, S0.ACCESSID as ACCESSID_ FROM [...]
"Something" had mistakenly aliased a custom field ACCESSID_, resulting in null values. The inner join to the children of the account resulted in an empty recordset.
Removing the stray "as ACCESSID_" resolved the issue.