My first MSCRM 4.0 upgrade was last night and so was my first MSCRM 4.0 issue. The issue after upgrading MSCRM and going to the site was "System.Data.SqlClient.SqlException: Invalid object name 'Entity'.". Screen shot is attached to the post if you want to see that.
Straight away you can see it is a SQL error, so I whipped out SQL profiler to see what is going on. I first limited to the _MSCRM and _METABASE databases (remember this is an upgrade) and started to watch what is happening. What I noticed is that it is running a "select * from entity" so entity must be a table or view. Checking the DB though I can't find it and running the select statement myself also failed. I also noticed that the _METABASE is not being called at all.
So using deployment manager I created a new tenant to see what that looked like. First thing I noticed is no _METABASE for the new tenant, and that if I ran the select statement in the new tenant DB myself it worked. So I look through that and what do I find a table named entity. But wait whats this before it? Normally tables are prefixed by dbo this isn't it's prefixed by metabase something or other, and then I remembered my SQL 2005 training on schemas.
See MSCRM 4.0 doesn't need 2 databases anymore since it uses schema's to put both the metabase and data in one database and can control the security seperately on that! This is also why SQL 2000 is not support, since it doesn't have schemas (atleast one of the reasons).
To resolve this I made sure the network service account (since we upgraded with that) had the same permissions on the original DB as on the new tenant DB and viola it worked!