Search code examples
sql-serversql-server-2005create-table

How can I restrict CREATE TABLE access for a user to a specific schema (sql server)?

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 see 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?

Solution

  • In a nutshell, yes.

    GRANT CREATE TABLE TO SomeUser
GRANT ALTER ON SCHEMA::AllowedSchema TO SomeUser
DENY ALTER ON SCHEMA::RestrictedSchema TO SomeUser