functions/update-d365user.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
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151

<#
    .SYNOPSIS
        Updates the user details in the database
         
    .DESCRIPTION
        Is capable of updating all the user details inside the UserInfo table to enable a user to sign in
         
    .PARAMETER DatabaseServer
        The name of the database server
         
        If on-premises or classic SQL Server, use either short name og Fully Qualified Domain Name (FQDN).
         
        If Azure use the full address to the database server, e.g. server.database.windows.net
         
    .PARAMETER DatabaseName
        The name of the database
         
    .PARAMETER SqlUser
        The login name for the SQL Server instance
         
    .PARAMETER SqlPwd
        The password for the SQL Server user
         
    .PARAMETER Email
        The search string to select which user(s) should be updated.
         
        The parameter supports wildcards. E.g. -Email "*@contoso.com*"
         
    .PARAMETER Company
        The company the user should start in.
         
    .EXAMPLE
        PS C:\> Update-D365User -Email "claire@contoso.com"
         
        This will search for the user with the e-mail address claire@contoso.com and update it with needed information based on the tenant owner of the environment
         
    .EXAMPLE
        PS C:\> Update-D365User -Email "*contoso.com"
         
        This will search for all users with an e-mail address containing 'contoso.com' and update them with needed information based on the tenant owner of the environment
         
    .NOTES
        Author: Rasmus Andersen (@ITRasmus)
        Author: Mötz Jensen (@Splaxi)
         
#>

function Update-D365User {
    [Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSUseShouldProcessForStateChangingFunctions", "")]
    [CmdletBinding()]
    param (
        [string]$DatabaseServer = $Script:DatabaseServer,

        [string]$DatabaseName = $Script:DatabaseName,

        [string]$SqlUser = $Script:DatabaseUserName,

        [string]$SqlPwd = $Script:DatabaseUserPassword,

        [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true)]
        [string]$Email,

        [string]$Company

    )
    begin {
        Invoke-TimeSignal -Start
        
        $UseTrustedConnection = Test-TrustedConnection $PSBoundParameters

        $SqlParams = @{ DatabaseServer = $DatabaseServer; DatabaseName = $DatabaseName;
            SqlUser = $SqlUser; SqlPwd = $SqlPwd
        }

        $SqlCommand = Get-SqlCommand @SqlParams -TrustedConnection $UseTrustedConnection
        
        $sqlCommand_Update = Get-SqlCommand @SqlParams -TrustedConnection $UseTrustedConnection

        try {
            $sqlCommand.Connection.Open()

            $sqlCommand_Update.Connection.Open()
        }
        catch {
            Write-PSFMessage -Level Host -Message "Something went wrong while working against the database" -Exception $PSItem.Exception
            Stop-PSFFunction -Message "Stopping because of errors"
            return
        }
    }

    process {
        $sqlCommand.CommandText = (Get-Content "$script:ModuleRoot\internal\sql\get-user.sql") -join [Environment]::NewLine

        $null = $sqlCommand.Parameters.Add("@Email", $Email.Replace("*", "%"))

        $sqlCommand_Update.CommandText = (Get-Content "$script:ModuleRoot\internal\sql\update-user.sql") -join [Environment]::NewLine

        try {
            Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $SqlCommand)
        
            $reader = $sqlCommand.ExecuteReader()

            while ($reader.Read() -eq $true) {
                Write-PSFMessage -Level Verbose -Message "Building the update statement with the needed details."

                $userId = "$($reader.GetString($($reader.GetOrdinal("ID"))))"
                $networkAlias = "$($reader.GetString($($reader.GetOrdinal("NETWORKALIAS"))))"

                $userAuth = Get-D365UserAuthenticationDetail $networkAlias

                $null = $sqlCommand_Update.Parameters.AddWithValue("@id", $userId)
                $null = $sqlCommand_Update.Parameters.AddWithValue("@networkDomain", $userAuth["NetworkDomain"])
                $null = $sqlCommand_Update.Parameters.AddWithValue("@sid", $userAuth["SID"])
                $null = $sqlCommand_Update.Parameters.AddWithValue("@identityProvider", $userAuth["IdentityProvider"])

                $null = $sqlCommand_Update.Parameters.AddWithValue("@Company", $Company)

                Write-PSFMessage -Level InternalComment -Message "Executing a script against the database." -Target (Get-SqlString $sqlCommand_Update)

                $null = $sqlCommand_Update.ExecuteNonQuery()

                $sqlCommand_Update.Parameters.Clear()
            }
        }
        catch {
            Write-PSFMessage -Level Host -Message "Something went wrong while working against the database" -Exception $PSItem.Exception
            Stop-PSFFunction -Message "Stopping because of errors"
            return
        }
        finally {
            $reader.close()
            $sqlCommand.Parameters.Clear()
        }
    }
    
    end {
        if ($sqlCommand_Update.Connection.State -ne [System.Data.ConnectionState]::Closed) {
            $sqlCommand_Update.Connection.Close()
        }

        $sqlCommand_Update.Dispose()
    
        if ($sqlCommand.Connection.State -ne [System.Data.ConnectionState]::Closed) {
            $sqlCommand.Connection.Close()
        }

        $sqlCommand.Dispose()

        Invoke-TimeSignal -End
    }
}