Schemas and user permissions

Question

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.

Answer 1

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...