CODEWITHSUNDEEP
sql-serversql-server-2005create-table
Rory
Rory

Reputation: 41807

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?

Upvotes: 2

Views: 4512

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

In a nutshell, yes.

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

Upvotes: 5

user803905
user803905

Reputation: 11

You can set user to schema owner

Upvotes: 0

Related Questions