question

PriyaJha-3992 avatar image
0 Votes"
PriyaJha-3992 asked SeeyaXi-msft answered

How to give an AD group admin access on a schema

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

sql-server-generalsql-server-transact-sqlazure-sql-databaseazure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SeeyaXi-msft avatar image
0 Votes"
SeeyaXi-msft answered

Hi @PriyaJha-3992,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.