SQL Server - grant create table to schema owner

Question

Using SQL Server 2014 Express. I'm new to SQL Server (more familiar with Oracle). I have a schema defined and a user defined as the schema owner. I cannot seem to find a way to allow the user to create tables inside the schema without granting db_owner role.

This will not work for my DB because there will be multiple schemas. With this database level role, ie db_owner, the user (schema owner) can create tables in any schema.

My goal is to have a schema owner user able to log into the DB and create tables and manage the schema and ONLY their own schema.

Can someone help me with this? I'm quite frustrated.

Thanks in advance.

Answer 1

After much research, I was able to figure out the answer to my own question:

"Create table" is only a database level permission, cannot be assigned at the schema level.

A user can be defined as the schema owner. If the user has "Create Table" permissions at the database level and is a schema owner, all tables will be created in the schema he/she owns.

A user cannot see other schemas unless granted "select" permission at the schema level by the schema owner (or someone with higher level authority). If the user has "Select" at the database level, he/she will see all schemas.