How to give an AD group admin access on a schema

Question

How to give an AD group admin access on a schema so that users of that group should be able to create objects only in that schema

Answer 1

Hey,
You need to execute the below set of command:
GRANT create table TO [login];
GRANT create view TO [login];
GRANT create Procedure TO [login];

GRANT alter 
    ON SCHEMA::[schema] TO [login];

GRANT SELECT
    ON SCHEMA::[schema] TO [login];

GRANT INSERT
    ON SCHEMA::[schema] TO [login];

GRANT UPDATE
    ON SCHEMA::[schema] TO [login];

GRANT DELETE
    ON SCHEMA::[schema] TO [login];

GRANT EXECUTE
    ON SCHEMA::[schema] TO [login];

Note :The CREATE TABLE is granted at the DB level and you grant the ALTER at the schema level. The combination of the 2 permissions will allow a user to actually create a table in the schema.

Also make sure :
Alter AUTHORIZATION ON SCHEMA::<<schemaname>> TO <<login>>;
so that dbo is not the default authorization

Answer 2

GRANT ALTER ON SCHEMA::ThatSchema TO [DOMAIN\Grp]
GRANT CREATE TABLE TO [DOMAIN\Grp]
GRANT CREATE PROCEDURE TO [DOMAIN\Grp]
-- etc

You need one GRANT CREATE per object type. This permission is on database level, but they also need to have ALTER permission on the schema.

Answer 3

Hi @Priya Jha ,

Agree with them.
You need one GRANT CREATE per object type or you can authorize together.
For a simple example:
GRANT SELECT, INSERT, UPDATE ON SCHEMA::[schema] TO [login];

Best regards,
Seeya

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.