Schemas and user permissions When our custom, home-grown CMS creates data tables using the GUI, it creates them against the site-specific user schema (i.e. sitename) instead of the dbo schema. 

The issue is that our site-specific users currently do not "see" these other schemas even though they are in the db_owner role. So somewhere there is a permissions setting missing which would grant them the ability to read and write against those tables. 

This issue has occurred when migrating to a new SQL server. Going from 2005 Workgroup Edition to 2008 Web Edition.
It could be as simple as:

GRANT SELECT ON SCHEMA::[sitename] TO [user];


If there are procedures and functions you will also need:

GRANT EXECUTE ON SCHEMA::[sitename] TO [user];


The full list of permissions applicable at the schema level is listed in the documentation:


GRANT Schema Permissions (Transact-SQL)


You can also set the default schema for any user, which may be helpful in some scenarios (for example, if they create tables without explicitly specifying the sitename schema):

ALTER USER [user] WITH DEFAULT_SCHEMA = [sitename];


Note that DENY overrides GRANT (and also overrides role membership rights, including db_owner), so check to be sure someone hasn't inadvertently blocked access to the schema(s). You can start your investigation if you find any rows here:

SELECT * FROM sys.database_permissions
  WHERE class_desc = N'SCHEMA' AND [state] = 'D';


Note that permissions can apply to a user indirectly (e.g. through AD group membership or database roles), so make sure you follow the bread crumbs for any database principal with a relevant DENY...