User Specific Schema

A clean pattern for letting a developer or integrator create and manage their own objects without touching anything else. The script creates the database user, creates a schema named after them, grants CREATE TABLE at the database level plus ALTER/CONTROL/DELETE/EXECUTE/INSERT/SELECT/UPDATE/VIEW DEFINITION on their schema, explicitly DENYs them ALTER on the dbo schema, and then hands ownership of their schema back to themselves (the temporary transfer to dbo in the middle is what makes the DENY stick).

USE <database>
GO

CREATE USER [<username>] FOR LOGIN [<username>]
GO

CREATE SCHEMA [<username>] AUTHORIZATION <username>
GO

ALTER AUTHORIZATION ON SCHEMA::<username> TO dbo
GO

GRANT CREATE TABLE TO <username>

GRANT ALTER				ON SCHEMA::<username> TO <username>
GRANT CONTROL			ON SCHEMA::<username> TO <username>
GRANT DELETE			ON SCHEMA::<username> TO <username>
GRANT EXECUTE			ON SCHEMA::<username> TO <username>
GRANT INSERT			ON SCHEMA::<username> TO <username>
GRANT SELECT			ON SCHEMA::<username> TO <username>
GRANT UPDATE			ON SCHEMA::<username> TO <username>
GRANT VIEW DEFINITION	ON SCHEMA::<username> TO <username>

DENY ALTER ON SCHEMA::dbo TO <username>
GO

ALTER AUTHORIZATION ON SCHEMA::<username> TO <username>
GO