internal/functions/Update-SqlDbOwner.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
function Update-SqlDbOwner {
    <#
    .SYNOPSIS
        Internal function. Updates specified database dbowner.
    #>

    [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")]
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [object]$Source,
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [object]$Destination,
        [string]$DbName,
        [PSCredential]$SourceSqlCredential,
        [PSCredential]$DestinationSqlCredential
    )

    $sourceServer = Connect-SqlInstance -SqlInstance $Source -SqlCredential $SourceSqlCredential
    $destServer = Connect-SqlInstance -SqlInstance $Destination -SqlCredential $SqlCredential

    $source = $sourceServer.DomainInstanceName
    $destination = $destServer.DomainInstanceName

    if ($DbName.length -eq 0) {
        $databases = ($sourceServer.Databases | Where-Object { $destServer.databases.name -contains $_.name -and $_.IsSystemObject -eq $false }).Name
    } else { $databases = $DbName }

    foreach ($DbName in $databases) {
        $destdb = $destServer.databases[$DbName]
        $dbowner = $sourceServer.databases[$DbName].owner

        if ($destdb.owner -ne $dbowner) {
            if ($destdb.Status -ne 'Normal') {
                Write-Message -Level Output -Message "Database status not normal. Skipping dbowner update."
                continue
            }

            if ($null -eq $dbowner -or $null -eq $destServer.logins[$dbowner]) {
                try {
                    $dbowner = ($destServer.logins | Where-Object { $_.id -eq 1 }).Name
                } catch {
                    $dbowner = "sa"
                }
            }

            try {
                if ($destdb.ReadOnly -eq $true) {
                    $changeroback = $true
                    Update-SqlDbReadOnly $destServer $DbName $false
                }

                $destdb.SetOwner($dbowner)
                Write-Output "Changed $DbName owner to $dbowner"

                if ($changeroback) {
                    Update-SqlDbReadOnly $destServer $DbName $true
                    $changeroback = $null
                }
            } catch {
                throw "Failed to update $DbName owner to $dbowner."
            }
        } else {
            Write-Message -Level Verbose -Message "Proper owner already set on $DbName"
        }
    }
}