Azure/Database/Set-AzureSQLPermissions/README.md

# Azure SQL Permissions Assignment

## Overview

This PowerShell script automates granting Azure SQL Database permissions to Managed Identities (or other Microsoft Entra identities).

Instead of connecting to each Azure SQL server using SQL Server Management Studio (SSMS), the script reads a CSV file containing the target databases and identities, then:

- Creates the database user (if it does not already exist)
- Grants the **db_datareader** role (optional)
- Grants the **db_datawriter** role (optional)
- Grants **EXECUTE** permission (optional)
- Logs all operations to a timestamped log file

The script is designed to be safely re-run multiple times.

---

# Prerequisites

## Azure CLI

Install Azure CLI:

https://learn.microsoft.com/cli/azure/install-azure-cli

Login before running the script:

```powershell
az login
```

If working with multiple subscriptions, select the required subscription:

```powershell
az account set --subscription "<Subscription Name or ID>"
```

---

## PowerShell

PowerShell 7.x is recommended.

---

## SqlServer PowerShell Module

The script uses `Invoke-Sqlcmd`.

Install it once:

```powershell
Install-Module SqlServer -Scope CurrentUser -Force
```

Verify installation:

```powershell
Get-Command Invoke-Sqlcmd
```

---

## Required Azure SQL Permissions

The account running the script must be able to:

- Connect to the Azure SQL Database using Microsoft Entra authentication
- Create database users
- Grant database permissions

Typically this means:

- Microsoft Entra SQL Administrator
- or a user with sufficient database permissions (for example `db_owner`).

---

# Files

## Set-AzureSQLPermissions.ps1

Main automation script.

### Parameters

| Parameter | Description |
|-----------|-------------|
| `-CsvPath` | Path to the CSV file to process. Optional, defaults to `AzureSQLPermissions.csv` next to the script. |

The script also supports the standard PowerShell `-WhatIf` and `-Confirm` switches (via `SupportsShouldProcess`): every database change (user creation, role grants, EXECUTE grant) is gated behind them, so `-WhatIf` shows what would be changed without touching any database.

---

## AzureSQLPermissions.csv

Contains the list of permissions to assign.

Example:

```csv
ServerName,DatabaseName,ManagedIdentityName,DataReader,DataWriter,Execute
sql-prod-01,SalesDB,vm-sales-prod,Yes,Yes,Yes
sql-prod-01,ReportingDB,vm-reporting,Yes,No,Yes
sql-test-01,TestDB,vm-test,Yes,Yes,No
```

### Columns

| Column | Description |
|---------|-------------|
| ServerName | Azure SQL logical server name (without `.database.windows.net`) |
| DatabaseName | Target database |
| ManagedIdentityName | Managed Identity or Microsoft Entra principal |
| DataReader | Yes / No |
| DataWriter | Yes / No |
| Execute | Yes / No |

Accepted values:

- Yes
- Y
- True
- 1

Any other value is treated as **No**.

---

# What the script does

For each CSV row:

1. Connects to the Azure SQL Database
2. Creates the user if it does not exist

```sql
CREATE USER [Identity] FROM EXTERNAL PROVIDER;
```

3. Optionally grants:

- db_datareader
- db_datawriter
- EXECUTE

---

# Logging

Each execution creates a log file:

```
AzureSQLPermissions_YYYYMMDD_HHMMSS.log
```

Example:

```
2026-06-30 09:12:11 [INFO] Starting Azure SQL permission script
2026-06-30 09:12:13 [INFO] Server: sql-prod-01
2026-06-30 09:12:14 [SUCCESS] Permissions successfully configured for 'vm-sales-prod'
2026-06-30 09:12:16 [ERROR] Principal could not be found.
```

---

# Running the script

Using the default CSV file location (`AzureSQLPermissions.csv` next to the script):

```powershell
./Set-AzureSQLPermissions.ps1
```

Using a different CSV file:

```powershell
./Set-AzureSQLPermissions.ps1 -CsvPath C:\data\perms.csv
```

Dry run - shows what would change without applying anything:

```powershell
./Set-AzureSQLPermissions.ps1 -WhatIf
```

---

# Error Handling

The script validates:

- Azure CLI is installed
- `Invoke-Sqlcmd` is available
- CSV file exists
- Azure access token can be obtained
- Each database operation

Errors are written to the log file.

If processing multiple databases, the script continues with the next entry after logging the error.

The script exits with code `0` on a completed run and `1` if a fatal error occurs before any databases are processed (missing prerequisites, missing/empty CSV, or failure to obtain an access token). Per-row failures are logged but do not change the exit code.

---

# Idempotency

The script is safe to run multiple times.

It checks whether:

- the user already exists
- the user is already a member of the requested database roles

Running the script repeatedly does not create duplicate users or duplicate role memberships.

---

# Notes

- The script uses Microsoft Entra authentication.
- SQL Authentication is not required.
- SSMS is not required.
- The script is suitable for bulk permission assignment across multiple Azure SQL databases.