VeeamSqlPerms

1.0

VeeamSqlPerms.psm1

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

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

    process {
        $ConfirmPreference = 'None'

        Write-Host "`n--- Starting permission assignment for [$Login] on [$SqlInstance] ---`n"

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

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

        Write-Host "Granting instance-level permissions:"
        Write-Host " - CONNECT SQL"
        Write-Host " - VIEW ANY DEFINITION"
        Write-Host " - VIEW SERVER STATE"
        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';
"@
                Log = @("db_backupoperator", "db_datareader")
            },
            @{
                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';
"@
                Log = @("db_backupoperator", "db_denydatareader")
            },
            @{
                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';
"@
                Log = @("db_backupoperator", "db_datareader", "db_datawriter")
            }
        )

        foreach ($item in $sysDbGrants) {
            try {
                Write-Host "Configuring system DB [$($item.Name)] with roles: $($item.Log -join ', ')"
                Invoke-DbaQuery -SqlInstance $SqlInstance -Database $item.Name -Query $item.Roles -EnableException
            }
            catch {
                Write-Warning "⚠️ Failed to configure system DB [$($item.Name)]: $_"
            }
        }

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

        foreach ($db in $userDbs) {
            try {
                $dbName = $db.Name
                $roles = @("public", "db_backupoperator", "db_denydatareader")

                if (-not (Get-DbaDbUser -SqlInstance $SqlInstance -Database $dbName -User $Login -ErrorAction SilentlyContinue)) {
                    Write-Host "Creating user [$Login] in database [$dbName]"
                    New-DbaDbUser -SqlInstance $SqlInstance -Database $dbName -Login $Login -Username $Login -Confirm:$false
                } else {
                    Write-Host "User [$Login] already exists in database [$dbName]"
                }

                foreach ($role in $roles) {
                    Write-Host "Assigning role [$role] in database [$dbName]"
                    Add-DbaDbRoleMember -SqlInstance $SqlInstance -Database $dbName -User $Login -Role $role -Confirm:$false -ErrorAction SilentlyContinue
                }
            }
            catch {
                Write-Warning "⚠️ Failed to process user DB [$($db.Name)]: $_"
            }
        }

        Write-Host "`n✅ All permissions applied for [$Login] on [$SqlInstance]`n"
    }
}