SQL Server – Grant Read-Only Access on [dbo] and Full Access on Own Schema

permissionsschemasql server

I created a user at the database level. I created a schema [jm] for them to use. I would like them to be able to create whatever database objects only within their own schema – but limit their abilities in [dbo] to purely read-only.

How can I accomplish that?

Best Answer

The correct grants are something like:

create user joe without login
go
create schema joe_schema
go

grant select on schema::dbo to joe;
alter authorization on schema::joe_schema to joe;

grant create table, 
      create procedure, 
      create view, 
      create function, 
      create type 
      to joe;

It's pretty important that you don't grant the user the ability to alter a schema owned by another user, especially one owned by dbo. If you do ownership chains will allow the user to skip permissions checks on the schema owner's objects in all schemas.