4

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.

0

1 Answer 1

5

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:

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.