How to restrict access to users to a specific schema?

Question

I am trying to grant users access to a specific schema. What this means is they will be able to access objects in a specific schema and create/alter/manage objects only in that specific schema.

have tried executing below code but that looks like it might provide access to create objects on the whole database.

CREATE ROLE [NewRole]
GO
CREATE SCHEMA [schema1]
GO
ALTER AUTHORIZATION ON SCHEMA::schema1 TO [NewRole]
GO
GRANT ALTER ON SCHEMA::[schema1] TO [NewRole]
GO
GRANT CREATE TABLE TO [NewRole]
GO
GRANT CREATE VIEW TO [NewRole]
GO
GRANT CREATE PROCEDURE TO [NewRole]

Any advise on how to proceed with this?

Thanks in advance.

Have you tested if that is indeed the case? Try that first (not saying it will or won't but you don't know for sure until you try it). — Igor, Sep 12 '19 at 18:01

score 0 · Answer 1 · answered Sep 12 '19 at 18:15

have tried executing below code but that looks like it might provide access to create objects on the whole database.

It does not. CREATE DATABASE is only grant-able on the whole database, but doesn't automatically grant you the ability to put the object in any schema. You also need ALTER permissions on the target schema, which you typically get by being the owner of the target schema.

So this is all you need:

CREATE ROLE [NewRole]
GO
CREATE SCHEMA [schema1]
GO
ALTER AUTHORIZATION ON SCHEMA::schema1 TO [NewRole]
GO
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO [NewRole]

You really should never need to grant ALTER on a schema.

How to restrict access to users to a specific schema?

1 Answers1