Direct Access to User

• Direct User Mapping: Granting direct access involves assigning specific permissions directly to individual users in the Azure SQL Database.
• Example: Using T-SQL commands or Azure Portal, you can create users within the database and grant them specific roles or permissions directly. For instance:

CREATE USER [username] FROM EXTERNAL PROVIDER;
ALTER ROLE [role_name] ADD MEMBER [username];

Access Through AD Group

• AD Group Membership: Instead of assigning permissions directly to users, you can grant access by adding users to an Azure Active Directory (AD) group and then assigning permissions to that group within the Azure SQL Database.
• Example: Create an AD group in Azure AD and add users to this group. Then, grant permissions to the AD group within the database:

CREATE USER [username] FROM EXTERNAL PROVIDER;
CREATE USER [AD_group_name] FROM EXTERNAL PROVIDER;
ALTER ROLE [role_name] ADD MEMBER [AD_group_name];

Comparison

• Direct Access:
  • Grants permissions directly to individual users.
  • Allows fine-grained control over user-level permissions.
  • May require more maintenance when managing access for multiple users.
• Access Through AD Group:
  • Grants permissions to a group of users through their membership in an AD group.
  • Simplifies management by applying permissions at the group level.
  • Streamlines access control, especially for larger user populations, as changes made to group membership are automatically reflected in database access.

Considerations

• Security: Always follow the principle of least privilege and ensure that users or groups have only the necessary permissions required for their tasks.
• Maintenance: Consider the manageability aspect when choosing between direct access and group-based access, especially for larger user sets.

Step by Step Explanation with Example

Granting Direct Access to User

Step 1: Create User in Azure SQL Database

You can create a user directly in the Azure SQL Database and assign permissions to that user.

Example:

CREATE USER [username] FROM EXTERNAL PROVIDER;

Step 2: Assign Permissions to User

Grant the necessary roles or permissions directly to the user within the database.

Example: Granting the user the db_datareader role:

ALTER ROLE db_datareader ADD MEMBER [username];

Granting Access Through Azure AD Group

Step 1: Create Azure AD Group

Create an Azure AD group in the Azure Active Directory and add users to this group.

Step 2: Create User in Azure SQL Database for AD Group

Create a user within the Azure SQL Database mapped to the Azure AD group.

CREATE USER [AD_group_name] FROM EXTERNAL PROVIDER;

Step 3: Assign Permissions to the AD Group User

Grant the necessary roles or permissions to the user mapped to the Azure AD group.

Example: Granting the AD group user the db_datareader role:

ALTER ROLE db_datareader ADD MEMBER [AD_group_name];

Example Scenario:

Let’s assume you have an Azure SQL Database called SampleDB, and you want to grant SELECT permission to a user named JohnDoe directly and to an Azure AD group named DatabaseReaders.

1. Granting Direct Access to JohnDoe:

CREATE USER JohnDoe FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER JohnDoe;

2. Setting Up Azure AD Group and Granting Access:

• Create an Azure AD group named DatabaseReaders in Azure Active Directory.
• Add users (e.g., JaneDoe, AlexSmith) to this group in Azure AD.
• Grant access to this group in Azure SQL Database:

CREATE USER DatabaseReaders FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER DatabaseReaders;

Considerations

• Permissions: Assign appropriate roles or permissions based on the required access level (e.g., db_owner, db_datareader, db_datawriter).
• Management: Regularly review and manage user access to maintain security and compliance.

How to Create AD Group in Azure Active Directory

Creating an Azure Active Directory (AD) group involves navigating the Azure portal and utilizing the Azure AD service. Here’s a step-by-step guide to creating an AD group

Steps to Create an AD Group in Azure Active Directory:

1. Access Azure Portal:

• Go to the Azure Portal and sign in with your Azure account.

2. Access Azure Active Directory Service:

• In the left-hand sidebar, select “Azure Active Directory.”

3. Create Group:

• Inside Azure AD, select “Groups” from the left-hand navigation pane.
• Click on the “+ New group” button at the top of the groups list.

4. Group Creation Details:

• Choose the group type: Security or Microsoft 365.
• Provide the required details:
  • Group name: Enter a unique name for the group.
  • Group description: Optionally, add a description.
  • Membership type: Choose either “Assigned” or “Dynamic User.”

5. Add Members (Optional):

• After creating the group, you can add members to the group by selecting it and navigating to the “Members” tab.

Example:

Here’s an example of creating a security group named “DatabaseReaders” in Azure AD via the Azure Portal:

1. Navigate to Azure Active Directory.
2. Click on “Groups” and select “+ New group.”
3. Choose “Security” as the group type.
4. Enter the group details:
   • Group name: DatabaseReaders
   • Group description: Group for database access.
   • Membership type: Assigned (for manually adding members).
5. Click “Create” to create the group.

Considerations:

• Ensure that the group name is unique within your Azure AD.
• Choose the appropriate group type based on your use case (Security, Microsoft 365).
• Assign meaningful descriptions for better management and identification.