<#
.SYNOPSIS
    Grants Azure SQL Database permissions to managed identities listed in a CSV file.

.DESCRIPTION
    Reads a CSV of ServerName/DatabaseName/ManagedIdentityName/DataReader/DataWriter/Execute
    rows, authenticates to Azure SQL using an access token from 'az account get-access-token',
    creates each managed identity as an external database user if it does not already exist,
    and grants the requested db_datareader/db_datawriter/EXECUTE permissions idempotently.
    Requires the Azure CLI ('az') and the SqlServer module (Invoke-Sqlcmd) to be installed,
    and an active 'az login' session with rights to request database access tokens.

.PARAMETER CsvPath
    Path to the input CSV file. Defaults to 'AzureSQLPermissions.csv' next to this script.

.EXAMPLE
    PS> .\Set-AzureSQLPermissions.ps1
    Processes AzureSQLPermissions.csv from the script folder and applies the permissions.

.EXAMPLE
    PS> .\Set-AzureSQLPermissions.ps1 -CsvPath C:\data\perms.csv -WhatIf
    Shows which grants would be applied without changing any database.

.NOTES
    Author:   Michal Horák
    Created:  2026-07-21
    Version:  1.1.0
    Changelog:
        1.1.0 - Added standard header, ShouldProcess/-WhatIf support, CsvPath parameter,
                Set-StrictMode/$ErrorActionPreference, function help blocks, exit 0 on success.
        1.0.0 - Initial version
#>

[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
param(
    [Parameter(Mandatory = $false, HelpMessage = 'Path to the CSV file containing permission rows.')]
    [ValidateNotNullOrEmpty()]
    [string]
    $CsvPath = (Join-Path $PSScriptRoot "AzureSQLPermissions.csv")
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

$LogFile = Join-Path $PSScriptRoot ("AzureSQLPermissions_{0}.log" -f (Get-Date -Format "yyyyMMdd_HHmmss"))

function Write-Log {
    <#
    .SYNOPSIS
        Writes a timestamped message to the console and to the run's log file.
    .PARAMETER Message
        The text to log.
    .PARAMETER Level
        Severity label prefixed to the log line (e.g. INFO, ERROR, SUCCESS). Defaults to INFO.
    .EXAMPLE
        Write-Log -Message "Starting" -Level INFO
    #>
    param(
        [string]$Message,
        [string]$Level = "INFO"
    )

    $Line = "{0} [{1}] {2}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Level, $Message
    Write-Host $Line
    Add-Content -Path $LogFile -Value $Line -ErrorAction Stop
}

function Test-Yes {
    <#
    .SYNOPSIS
        Interprets a CSV cell value as a boolean flag.
    .PARAMETER Value
        The raw CSV cell text (e.g. "Yes", "Y", "True", "1").
    .EXAMPLE
        Test-Yes -Value 'Yes'
    #>
    param([string]$Value)
    return $Value -match '^(Yes|Y|True|1)$'
}

Write-Log "Starting Azure SQL permission script"
Write-Log "CSV file: $CsvPath"
Write-Log "Log file: $LogFile"

try {
    if (!(Test-Path $CsvPath)) {
        throw "CSV file not found: $CsvPath"
    }

    if (-not (Get-Command az -ErrorAction SilentlyContinue)) {
        throw "Azure CLI 'az' is not available."
    }

    if (-not (Get-Command Invoke-Sqlcmd -ErrorAction SilentlyContinue)) {
        throw "Invoke-Sqlcmd is not available. Install it with: Install-Module SqlServer -Scope CurrentUser -Force"
    }

    $Permissions = Import-Csv -Path $CsvPath -ErrorAction Stop

    if (-not $Permissions) {
        throw "CSV file is empty."
    }

    $AccessToken = az account get-access-token `
        --resource https://database.windows.net/ `
        --query accessToken `
        -o tsv

    if (-not $AccessToken) {
        throw "Failed to obtain Azure SQL access token. Run 'az login' first."
    }
}
catch {
    Write-Log $_.Exception.Message "ERROR"
    exit 1
}

foreach ($Entry in $Permissions) {

    $ServerName = $Entry.ServerName.Trim()
    $DatabaseName = $Entry.DatabaseName.Trim()
    $ManagedIdentityName = $Entry.ManagedIdentityName.Trim()

    $GrantDataReader = Test-Yes $Entry.DataReader
    $GrantDataWriter = Test-Yes $Entry.DataWriter
    $GrantExecute = Test-Yes $Entry.Execute

    Write-Log "--------------------------------------------"
    Write-Log "Server: $ServerName"
    Write-Log "Database: $DatabaseName"
    Write-Log "Managed Identity: $ManagedIdentityName"
    Write-Log "DataReader: $GrantDataReader, DataWriter: $GrantDataWriter, Execute: $GrantExecute"

    try {
        if ([string]::IsNullOrWhiteSpace($ServerName) -or
            [string]::IsNullOrWhiteSpace($DatabaseName) -or
            [string]::IsNullOrWhiteSpace($ManagedIdentityName)) {
            throw "Missing ServerName, DatabaseName or ManagedIdentityName in CSV row."
        }

        $PermissionSql = ""

        if ($GrantDataReader) {
            $PermissionSql += @"

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_role_members rm
    JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
    JOIN sys.database_principals u ON rm.member_principal_id = u.principal_id
    WHERE r.name = 'db_datareader'
      AND u.name = N'$ManagedIdentityName'
)
BEGIN
    ALTER ROLE db_datareader ADD MEMBER [$ManagedIdentityName];
END;
"@
        }

        if ($GrantDataWriter) {
            $PermissionSql += @"

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_role_members rm
    JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
    JOIN sys.database_principals u ON rm.member_principal_id = u.principal_id
    WHERE r.name = 'db_datawriter'
      AND u.name = N'$ManagedIdentityName'
)
BEGIN
    ALTER ROLE db_datawriter ADD MEMBER [$ManagedIdentityName];
END;
"@
        }

        if ($GrantExecute) {
            $PermissionSql += @"

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_permissions p
    JOIN sys.database_principals pr
        ON p.grantee_principal_id = pr.principal_id
    WHERE pr.name = N'$ManagedIdentityName'
      AND p.permission_name = 'EXECUTE'
      AND p.state IN ('G','W')
)
BEGIN
    GRANT EXECUTE TO [$ManagedIdentityName];
END;
"@
        }

        $Query = @"
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE name = N'$ManagedIdentityName'
)
BEGIN
    CREATE USER [$ManagedIdentityName] FROM EXTERNAL PROVIDER;
END;

$PermissionSql
"@

        if ($PSCmdlet.ShouldProcess("$ServerName/$DatabaseName", "Grant permissions to '$ManagedIdentityName'")) {
            Invoke-Sqlcmd `
                -ServerInstance "$ServerName.database.windows.net" `
                -Database $DatabaseName `
                -AccessToken $AccessToken `
                -Query $Query `
                -ErrorAction Stop

            Write-Log "Permissions successfully configured for '$ManagedIdentityName' on $ServerName / $DatabaseName" "SUCCESS"
        }
    }
    catch {
        Write-Log "FAILED configuring permissions for '$ManagedIdentityName' on $ServerName / $DatabaseName" "ERROR"
        Write-Log $_.Exception.Message "ERROR"
        continue
    }
}

Write-Log "Script finished"
exit 0