VeeamSqlPerms

1.0.3

VeeamSqlPerms.psm1

function Set-VeeamPerms {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory, ValueFromPipeline)]
        [string]$SqlInstance,

        [Parameter(Mandatory)]
        [string]$Login,

        [Parameter()]
        [switch]$AddLocalAdmin,

        [Parameter()]
        [string]$LogPath,

        [Parameter()]
        [string]$ExportPath
    )

    process {
        $results = @()
        $ConfirmPreference = 'None'
        $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

        function Write-Log {
            param ($Message)
            $logEntry = "[$(Get-Date -Format "yyyy-MM-dd HH:mm:ss")] $Message"
            Write-Host $logEntry
            if ($LogPath) {
                Add-Content -Path $LogPath -Value $logEntry
            }
        }

        Write-Log "--- Starting permission assignment for [$Login] on [$SqlInstance] ---"

        if (-not (Get-DbaLogin -SqlInstance $SqlInstance -Login $Login -ErrorAction SilentlyContinue)) {
            Write-Log "Creating SQL login: $Login"
            New-DbaLogin -SqlInstance $SqlInstance -Login $Login -Confirm:$false
        } else {
            Write-Log "Login [$Login] already exists."
        }

        Write-Log "Assigning server role: dbcreator"
        Set-DbaLogin -SqlInstance $SqlInstance -Login $Login -AddRole 'dbcreator' -Confirm:$false

        Write-Log "Granting instance-level permissions..."
        Invoke-DbaQuery -SqlInstance $SqlInstance -Query @"
GRANT CONNECT SQL TO [$Login];
GRANT VIEW ANY DEFINITION TO [$Login];
GRANT VIEW SERVER STATE TO [$Login];
"@

        $sysDbGrants = @(
            @{ Name = "master"; Roles = @"
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'$Login')
    CREATE USER [$Login] FOR LOGIN [$Login];
EXEC sp_addrolemember N'db_backupoperator', N'$Login';
EXEC sp_addrolemember N'db_datareader', N'$Login';
"@ },
            @{ Name = "model"; Roles = @"
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'$Login')
    CREATE USER [$Login] FOR LOGIN [$Login];
EXEC sp_addrolemember N'db_backupoperator', N'$Login';
EXEC sp_addrolemember N'db_denydatareader', N'$Login';
"@ },
            @{ Name = "msdb"; Roles = @"
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'$Login')
    CREATE USER [$Login] FOR LOGIN [$Login];
EXEC sp_addrolemember N'db_backupoperator', N'$Login';
EXEC sp_addrolemember N'db_datareader', N'$Login';
EXEC sp_addrolemember N'db_datawriter', N'$Login';
"@ }
        )

        foreach ($item in $sysDbGrants) {
            try {
                Write-Log "Configuring system DB [$($item.Name)]"
                Invoke-DbaQuery -SqlInstance $SqlInstance -Database $item.Name -Query $item.Roles -EnableException
            } catch {
                Write-Log "[WARNING] Failed to configure [$($item.Name)]: $_"
            }
        }

        $userDbs = Get-DbaDatabase -SqlInstance $SqlInstance | Where-Object { $_.Name -notin @("master", "model", "msdb", "tempdb") }

        foreach ($db in $userDbs) {
            try {
                $dbName = $db.Name
                if (-not (Get-DbaDbUser -SqlInstance $SqlInstance -Database $dbName -User $Login -ErrorAction SilentlyContinue)) {
                    Write-Log "Creating user [$Login] in DB [$dbName]"
                    New-DbaDbUser -SqlInstance $SqlInstance -Database $dbName -Login $Login -Username $Login -Confirm:$false
                }

                foreach ($role in @("public", "db_backupoperator", "db_denydatareader")) {
                    Add-DbaDbRoleMember -SqlInstance $SqlInstance -Database $dbName -User $Login -Role $role -Confirm:$false -ErrorAction SilentlyContinue
                    Write-Log "Assigned [$role] role in DB [$dbName]"
                }
            } catch {
                Write-Log "[WARNING] Failed to assign roles in [$dbName]: $_"
            }
        }

        if ($AddLocalAdmin) {
            try {
                $serverName = ($SqlInstance -split '\\')[0]
                Write-Log "Adding [$Login] to local Administrators group on [$serverName]"
                $domain, $username = $Login -split '\\'

                Invoke-Command -ComputerName $serverName -ScriptBlock {
                    param ($Domain, $Username)
                    $userPath = "WinNT://$Domain/$Username"
                    $group = [ADSI]"WinNT://./Administrators,group"
                    $isMember = $group.psbase.Invoke("IsMember", $userPath)
                    if (-not $isMember) {
                        $group.Add($userPath)
                        Write-Output "Successfully added [$Domain\$Username] to Administrators"
                    } else {
                        Write-Output "[$Domain\$Username] already in Administrators group"
                    }
                } -ArgumentList $domain, $username | ForEach-Object { Write-Log $_ }
            } catch {
                Write-Log "[ERROR] Failed to add [$Login] to local Administrators on ${serverName}: $_"
            }
        }

        Write-Log "✅ Permissions applied for [$Login] on [$SqlInstance]"

        $results += [PSCustomObject]@{
            SqlInstance     = $SqlInstance
            Login           = $Login
            Timestamp       = $timestamp
            LocalAdminAdded = $AddLocalAdmin.IsPresent
        }

        # Optional export
        if ($ExportPath) {
            $results | Export-Csv -Path $ExportPath -NoTypeInformation -Append
            Write-Log "Exported summary to $ExportPath"
        }
    }
}