CODEWITHSUNDEEP
sqlsql-serverdatabase-securityazure-synapse
Vardhan
Vardhan

Reputation: 422

How to restrict access to users to a specific schema?

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.

Upvotes: 0

Views: 3858

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89406

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.

Upvotes: 0

Related Questions