HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Granting a user read-only access on [dbo] but full access on their own schema

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
fullreaduserbutgrantingschemadboownonlyaccess

Problem

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?

Solution

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.

Code Snippets

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;

Context

StackExchange Database Administrators Q#201933, answer score: 3

Revisions (0)

No revisions yet.