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?

2 Answers 2


The correct grants are something like:

create user joe without login
create schema joe_schema

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.


Take a look at Schema-level permissions:

GRANT Schema Permissions

You should be able to do something like:

GRANT SELECT on SCHEMA::[dbo] TO [user_name];



I believe the SELECT permission is implied if that User is the owner of that Schema (at least that is how it appears to be working for me).

Please be sure to read all of that linked documentation as there are implications of various permissions that you should be aware of, such as with the ALTER permission.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.