patternsqlMinor
Granting a user read-only access on [dbo] but full access on their own schema
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?
How can I accomplish that?
Solution
The correct grants are something like:
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.
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.