SQL Server - Give user permission to create table in their own schema, but not in dbo schema

Question

I am not a DBA, but have been asked to help set up a teaching database for some internal company SQL classes. I am using an Azure SQL Server database where each student needs to be able to create tables, views and stored procedures for themselves but not see any of the other students' work. I am unable, due to cost constraints, to create an Azure DB for each student and load it with the raw data, so I am trying to do it all in one DB.

Here was my idea:

Create a schema for each student and give them permissions to write to it.
Give them permissions to read from the dbo schema where the raw data will be stored, but not write to it (I dont want to give them the opportunity to overwrite the raw tables).
Hide all the other students schemas so that the student can only see their tables/views/sprocs and those in the dbo schema.

I have been able to create logins and users and schemas and give read permissions to dbo schema, but i can't give the student full control over their own schema.

What i need advice on is, how can i set up the database to let each student have their own schema with write access, but have read-only access to the dbo schema? It seems like i can lock down the students schema, but cant give them write access to it without write access to the entire database. Is there a way to separate them this way?

Also, is there a better way to do what I want with regards to separating students' environments on the same database?

This is what i have tried so far:

USE MASTER 
CREATE LOGIN student1 WITH PASSWORD = 'T3mpSuperSecret!';     

USE class_DB 
CREATE USER student1 FOR LOGIN student1; 
CREATE SCHEMA st1 AUTHORIZATION student1; 
GRANT SELECT ON SCHEMA :: dbo TO student1 
GRANT CONTROL ON SCHEMA :: st1 TO student1

I then try to select one of the base tables from dbo into the new schema:

select * 
into st1.inventories
from dbo.inventories

I get:

Msg 262, Level 14, State 1, Line 1
CREATE TABLE permission denied in database 'class_DB'.

It looks like create table permissions is on a DB level, not the schema level. Is there a way to authorize create table permissions on the schema level?

I hope this all makes sense. Again, I am not a DBA, so any help would be appreciated.

Thanks!

user_2029983 · Answer

Granting CREATE TABLE, and other permissions doesn't do anything is that USER doesn't also have the ALTER permission. As such, all you need to is GRANT the USER the CREATE TABLE, CREATE VIEW, etc permissions on their specific schema, and then SELECT on the dbo schema.

This uses with WITHOUT LOGIN as an example, as I don't have the LOGIN objects, but this demonstrates the granted, and implicitly denied, permissions:

CREATE DATABASE SampleDB;
GO
USE SampleDB;
GO

CREATE TABLE dbo.SomeTable (SomeID int IDENTITY);

INSERT INTO dbo.SomeTable
DEFAULT VALUES;
GO

CREATE USER student1 WITHOUT LOGIN;
GO
CREATE SCHEMA st1 AUTHORIZATION student1;
GO
GRANT SELECT ON SCHEMA::dbo TO student1;
GRANT CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE TO student1;
GO

CREATE USER student2 WITHOUT LOGIN;
GO
CREATE SCHEMA st2 AUTHORIZATION student2;
GO
GRANT SELECT ON SCHEMA::dbo TO student2;
GRANT CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE TO student2;
GO

EXECUTE AS USER = 'student1';
GO

CREATE TABLE st1.TestTable (ID int);
INSERT INTO st1.TestTable
SELECT SomeID
FROM dbo.SomeTable;
GO

SELECT *
FROM st1.TestTable;
GO

REVERT;
GO

EXECUTE AS USER = 'student2';
GO

CREATE PROC st2.TestProc AS
BEGIN
    SELECT *
    FROM st1.TestTable;
END;
GO

EXEC st2.TestProc; --fails
GO
CREATE TABLE st1.TestTable (ID int); --fails too
GO

REVERT;
GO

USE master;
GO

DROP DATABASE SampleDB;

SQL Server - Give user permission to create table in their own schema, but not in dbo schema

1 Answers