internal/Set-DatabasePermissions.ps1
|
function Set-DatabasePermissions { [CmdletBinding()] Param( [Parameter(Mandatory = $false, Position = 0)] [string] $SqlInstance, [Parameter(Mandatory = $false, Position = 1)] [string] $Database ) Begin { $ErrorActionPreference = 'Stop' $env:EDWServer = $SqlInstance $db = $Database $filepath = "\\dbaprod02\SqlPermissions\$env:EDWServer" if (!(Test-Path -Path $filepath)) { Write-PSFMessage -Level Warning -Message "$filepath is invalid" -ErrorRecord $_ -ErrorAction Stop Break(1) } $file = (Get-ChildItem -Path $filepath\$db.sql).Name } Process { $presentState = (Get-DbaDbState -SqlInstance $env:EDWServer -Database $db).RW Write-PSFMessage -Level Host -Message "$db is $presentState" if ($presentState -eq 'READ_ONLY') { Write-PSFmessage -Level Host -Message "Setting $env:EDWServer to read_write" Set-DbaDbState -SqlInstance $env:EDWserver -Database $db -ReadWrite -Force -ErrorAction Stop | Out-Null try { Invoke-DbaQuery -SqlInstance $env:EDWServer -InputFile $filepath\$file -Database $db -ErrorAction Stop Write-PSFmessage -Level Host -Message "Applied permissions to $db on $env:EDWServer from $filepath\$file" } catch { Write-PSFMessage -Level Warning -Message "Failed to apply permissions" -ErrorRecord $_ -ErrorAction Stop } Write-PSFmessage -Level Host -Message "Setting $env:EDWServer back to READ_ONLY" Set-DbaDbState -SqlInstance $env:EDWserver -Database $db -ReadOnly -Force -ErrorAction Stop | Out-Null } else { try { Invoke-DbaQuery -SqlInstance $env:EDWServer -InputFile $filepath\$file -Database $db -ErrorAction Stop Write-PSFmessage -Level Host -Message "Applied permissions to $db on $env:EDWServer from $filepath\$file" } catch { Write-PSFMessage -Level Warning -Message "Failed to apply permissions" -ErrorRecord $_ -ErrorAction Stop } } } } |