sql-serverdatabasedatabase-designazure-sql-databasedatabase-administration

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


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:

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!


Solution

  • 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;