Authority create table on custom schema

Question

My default schema dbo.

I not give my users permission to create table.

User authority ; datareader,datawriter

CREATE TABLE dbo.newtable ... 

CREATE TABLE permission denied in database 'DB'

But, my users i want to be able to create a table in the other schema. How can I do that?

CREATE TABLE support.newtable ...

Thanks for your help.

Answer 1

1- Create DB Role [db_support]

2- GRANT ALTER ON SCHEMA::support TO [db_support] DENY ALTER ON SCHEMA::dbo TO [db_support]

3- GRANT CREATE TABLE TO [db_support]

4- CREATE TABLE sup.asd (asd int)

Command(s) completed successfully

Answer 2

you need to deny permission on a schema level not at the database level.

please refer to this MSDN article.

for your scenario it would look like

DENY CREATE ON SCHEMA::dbo TO 'user'