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.
Best Answer
It could be as simple as:
If there are procedures and functions you will also need:
The full list of permissions applicable at the schema level is listed in the documentation:
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):Note that
DENY
overridesGRANT
(and also overrides role membership rights, includingdb_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: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
...