2

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

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.

| improve this answer | |
0

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

and:

GRANT CREATE, EXECUTE ON SCHEMA::[jm] 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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