Granting Admin Access on a Schema in Azure Database / Synapse Dedicated Pool to an Azure AD Group

Problem Statement :

Is it possible to provide admin access to an Azure AD group on a specific schema within Azure SQL Database / Synapse Dedicated Pool.

Prerequisites :

1. AD Admin Account

Solution :

1. To grant access to an Azure AD group, one needs to either login to Azure SQL Database / Synapse Dedicated Pool via SQL Server Management Studio, Azure Data Studio, or Azure Query Editor using an Active Directory Admin account.
2. Execute the below Set of SQL Statements on the logged in system :

You need one GRANT CREATE per object type.

GRANT CREATE Table TO [<<AD Group Name>>];
GRANT CREATE View TO [<<AD Group Name>>];
GRANT CREATE Procedure TO [<<AD Group Name>>];

Now Grant DDL, DML and DQL access at Schema level.

 GRANT ALTER ON SCHEMA::[<<Schema Name>>] TO [<<AD Group Name>>];
    
 GRANT SELECT ON SCHEMA::[<<Schema Name>>] TO [<<AD Group Name>>];
    
 GRANT INSERT ON SCHEMA::[<<Schema Name>>] TO [<<AD Group Name>>];
    
 GRANT UPDATE ON SCHEMA::[<<Schema Name>>] TO [<<AD Group Name>>];
    
 GRANT DELETE ON SCHEMA::[<<Schema Name>>] TO [<<AD Group Name>>];
    
 GRANT EXECUTE ON SCHEMA::[<<Schema Name>>] TO [<<AD Group Name>>];

Note : The CREATE TABLE permission is granted at the database level and the ALTER permission is granted at the schema level. The combination of these 2 permissions will allow a user to actually create a table within the particular schema.

For better security, also make sure to ALTER the authorization by executing the below statement so the “dbo” is not the default authorization. This is to avoid the users any access to impact / manipulate other schemas having same authorization as the schema in which they have been granted Admin access.

One can verify this by executing “Select * from SYS.SCHEMAS”

Alter AUTHORIZATION ON SCHEMA::<<Schema Name>> TO <<AD Group Name>>;

How can one impact other schema objects via the schema in which user has admin access (This is applicable if the schemas are tagged to same AUTHORIZATION / Principal_Id from Sys.Schemas output) :

1. As the user has Admin access and Create Stored Procedure access on a schema, the user can create a Stored Procedure dropping objects from other schema ( sharing same Authorization)

The below Statements would enable the user to drop a table from another schema via his Admin access on a particular schema due to common authorization link between the 2 schemas.

Note : The user can perform any DDL, DML and DQL on the non admin schema via the Admin schema.

CREATE PROCEDURE <<AdminSchema>>.<<ProcedureName>> AS 
DROP Table <<NonAdminSchemaButWithSameAUthorization>>.<<TableName>>
GO;


EXEC <<AdminSchema>>.<<ProcedureName>>