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