I'd like to allow some users to create tables in a particular schema, but not be able to create tables in other schemas. How can I achieve this?
I s开发者_开发技巧ee the CREATE TABLE help says
Requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.
Does that mean I can give ALTER permission to the schema and restrict ALTER permissions on all other schemas?
In a nutshell, yes.
GRANT CREATE TABLE TO SomeUser
GRANT ALTER ON SCHEMA::AllowedSchema TO SomeUser
DENY ALTER ON SCHEMA::RestrictedSchema TO SomeUser
You can set user to schema owner
精彩评论