functions/Set-DbaLogin.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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
function Set-DbaLogin {

    <#
    .SYNOPSIS
    Set-DbaLogin makes it possible to make changes to one or more logins.
 
    .DESCRIPTION
    Set-DbaLogin will enable you to change the password, unlock, rename, disable or enable, deny or grant login privileges to the login.
    It's also possible to add or remove server roles from the login.
 
    .PARAMETER SqlInstance
    SQL Server instance. You must have sysadmin access and server version must be SQL Server version 2000 or greater.
 
    .PARAMETER SqlCredential
    Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use:
    $scred = Get-Credential, then pass $scred object to the -SqlCredential parameter.
    To connect as a different Windows user, run PowerShell as that user.
 
    .PARAMETER Login
    The login that needs to be changed
 
    .PARAMETER Password
    The new password for the login This can be either a credential or a secure string.
 
    .PARAMETER Unlock
    Switch to unlock an account. This will only be used in conjunction with the -Password parameter.
    The default is false.
 
    .PARAMETER MustChange
    Does the user need to change his/her password. This will only be used in conjunction with the -Password parameter.
    The default is false.
 
    .PARAMETER NewName
    The new name for the login.
 
    .PARAMETER Disable
    Disable the login
 
    .PARAMETER Enable
    Enable the login
 
    .PARAMETER DenyLogin
    Deny access to SQL Server
 
    .PARAMETER GrantLogin
    Grant access to SQL Server
 
    .PARAMETER AddRole
    Add one or more server roles to the login
    The following roles can be used "bulkadmin", "dbcreator", "diskadmin", "processadmin", "public", "securityadmin", "serveradmin", "setupadmin", "sysadmin".
 
    .PARAMETER RemoveRole
    Remove one or more server roles to the login
    The following roles can be used "bulkadmin", "dbcreator", "diskadmin", "processadmin", "public", "securityadmin", "serveradmin", "setupadmin", "sysadmin".
 
    .PARAMETER EnableException
    By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
    This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
    Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
 
    .NOTES
    Original Author: Sander Stad (@sqlstad, sqlstad.nl)
    Tags: Login
 
    Website: https://dbatools.io
    Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
    License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
    .LINK
    https://dbatools.io/Set-DbaLogin
 
    .EXAMPLE
    $password = ConvertTo-SecureString "PlainTextPassword" -AsPlainText -Force
    $cred = New-Object System.Management.Automation.PSCredential ("username", $password)
    Set-DbaLogin -SqlInstance sql1 -Login login1 -Password $cred -Unlock -MustChange
 
    Set the new password for login1 using a credential, unlock the account and set the option
    that the usermust change password at next logon.
 
    .EXAMPLE
    Set-DbaLogin -SqlInstance sql1 -Login login1 -Enable
 
    Enable the login
 
    .EXAMPLE
    Set-DbaLogin -SqlInstance sql1 -Login login1, login2, login3, login4 -Enable
 
    Enable multiple logins
 
    .EXAMPLE
    Set-DbaLogin -SqlInstance sql1, sql2, sql3 -Login login1, login2, login3, login4 -Enable
 
    Enable multiple logins on multiple instances
 
    .EXAMPLE
    Set-DbaLogin -SqlInstance sql1 -Login login1 -Disable
 
    Disable the login
 
    .EXAMPLE
    Set-DbaLogin -SqlInstance sql1 -Login login1 -DenyLogin
 
    Deny the login to connect to the instance
 
    .EXAMPLE
    Set-DbaLogin -SqlInstance sql1 -Login login1 -GrantLogin
 
    Grant the login to connect to the instance
 
    .EXAMPLE
    Set-DbaLogin -SqlInstance sql1 -Login test -AddRole serveradmin
 
    Add the server role "serveradmin" to the login
 
    .EXAMPLE
    Set-DbaLogin -SqlInstance sql1 -Login test -RemoveRole bulkadmin
 
    Remove the server role "bulkadmin" to the login
 
#>


    [CmdletBinding()]

    param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [parameter(Mandatory = $true)]
        [string[]]$Login,
        [object]$Password,
        [switch]$Unlock,
        [switch]$MustChange,
        [string]$NewName,
        [switch]$Disable,
        [switch]$Enable,
        [switch]$DenyLogin,
        [switch]$GrantLogin,
        [ValidateSet("bulkadmin", "dbcreator", "diskadmin", "processadmin", "public", "securityadmin", "serveradmin", "setupadmin", "sysadmin")]
        [string[]]$AddRole,
        [ValidateSet("bulkadmin", "dbcreator", "diskadmin", "processadmin", "public", "securityadmin", "serveradmin", "setupadmin", "sysadmin")]
        [string[]]$RemoveRole,
        [switch][Alias('Silent')]$EnableException
    )

    begin {

        # Check the parameters
        if ($Login -eq $NewName) {
            Stop-Function -Message "Login name is the same as the value in -NewName" -Target $Login -Continue
        }

        if ($Disable -and $Enable) {
            Stop-Function -Message "You cannot use both -Enable and -Disable together" -Target $Login -Continue
        }

        if ($GrantLogin -and $DenyLogin) {
            Stop-Function -Message "You cannot use both -GrantLogin and -DenyLogin together" -Target $Login -Continue
        }

        # Check the password
        if ($Password) {
            switch ($Password.GetType().Name) {
                "PSCredential" { $newPassword = $Password.Password}
                "SecureString" { $newPassword = $Password}
            }
        }
        else {
        }

    }

    process {
        if (Test-FunctionInterrupt) { return }

        foreach ($instance in $sqlinstance) {
            # Try connecting to the instance
            Write-Message -Message "Attempting to connect to $instance" -Level Verbose
            try {
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $SqlCredential -MinimumVersion 9
            }
            catch {
                Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue
            }

            # Get all the logins
            $allLogins = $server.Logins | Where-Object {($_.IsSystemObject -eq $false) -and ($_.Name -notlike '##*')}
            $logins = $server.Logins | Where-Object {$Login -contains $_.Name}

            # Loop through all the logins
            foreach ($l in $logins) {

                # Create the notes
                $notes = @()

                # Change the name
                if ($NewName) {
                    # Check if the new name doesn't already exist
                    if ($allLogins.Name -notcontains $NewName) {
                        try {
                            $l.Rename($NewName)
                        }
                        catch {
                            $notes += "Couldn't rename login"
                            Stop-Function -Message "Something went wrong changing the name for $l" -Target $l -ErrorRecord $_ -Continue
                        }
                    }
                    else {
                        $notes += "New login name already exists"
                        Write-Message -Message "New login name $NewName already exists on $instance" -Level Verbose
                    }
                }

                # Change the password
                if ($Password) {
                    try {
                        $l.ChangePassword($newPassword, $Unlock, $MustChange)
                        $passwordChanged = $true
                    }
                    catch {
                        $notes += "Couldn't change password"
                        $passwordChanged = $false
                        Stop-Function -Message "Something went wrong changing the password for $l" -Target $l -ErrorRecord $_ -Continue
                    }
                }

                # Disable the login
                if ($Disable) {
                    if ($l.IsDisabled) {
                        Write-Message -Message "Login $l is already disabled" -Level Verbose
                    }
                    else {
                        try {
                            $l.Disable()
                        }
                        catch {
                            $notes += "Couldn't disable login"
                            Stop-Function -Message "Something went wrong disabling $l" -Target $l -ErrorRecord $_ -Continue
                        }
                    }
                }

                # Enable the login
                if ($Enable) {
                    if (-not $l.IsDisabled) {
                        Write-Message -Message "Login $l is already enabled" -Level Verbose
                    }
                    else {
                        try {
                            $l.Enable()
                        }
                        catch {
                            $notes += "Couldn't enable login"
                            Stop-Function -Message "Something went wrong enabling $l" -Target $l -ErrorRecord $_ -Continue
                        }
                    }
                }

                # Deny access
                if ($DenyLogin) {
                    if ($l.DenyWindowsLogin) {
                        Write-Message -Message "Login $l already has login access denied" -Level Verbose
                    }
                    else {
                        $l.DenyWindowsLogin = $true
                    }
                }

                # Grant access
                if ($GrantLogin) {
                    if (-not $l.DenyWindowsLogin) {
                        Write-Message -Message "Login $l already has login access granted" -Level Verbose
                    }
                    else {
                        $l.DenyWindowsLogin = $false
                    }
                }

                # Add server roles to login
                if ($AddRole) {
                    # Loop through each of the roles
                    foreach ($role in $AddRole) {
                        try {
                            $l.AddToRole($role)
                        }
                        catch {
                            $notes += "Couldn't add role $role"
                            Stop-Function -Message "Something went wrong adding role $role to $l" -Target $l -ErrorRecord $_ -Continue
                        }
                    }
                }

                # Remove server roles from login
                if ($RemoveRole) {
                    # Loop through each of the roles
                    foreach ($role in $RemoveRole) {
                        try {
                            $server.Roles[$role].DropMember($l.Name)
                        }
                        catch {
                            $notes += "Couldn't remove role $role"
                            Stop-Function -Message "Something went wrong removing role $role to $l" -Target $l -ErrorRecord $_ -Continue
                        }
                    }
                }

                # Alter the login to make the changes
                $l.Alter()

                # Retrieve the server roles for the login
                $roles = Get-DbaRoleMember -SqlInstance $instance -IncludeServerLevel | Where-Object {$_.Database -eq $null -and $_.Member -eq $l.Name}

                # Check if there were any notes to include in the results
                if ($notes) {
                    $notes = $notes | Get-Unique
                    $notes = $notes -Join ';'
                }
                else {
                    $notes = $null
                }

                # Return the results
                [PSCustomObject]@{
                    ComputerName       = $server.NetName
                    InstanceName       = $server.ServiceName
                    SqlInstance        = $server.DomainInstanceName
                    LoginName          = $l.Name
                    DenyLogin          = $l.DenyWindowsLogin
                    IsDisabled         = $l.IsDisabled
                    IsLocked           = $l.IsLocked
                    MustChangePassword = $l.MustChangePassword
                    PasswordChanged    = $passwordChanged
                    ServerRole         = $roles.Role -join ","
                    Notes              = $notes
                } | Select-DefaultView -ExcludeProperty Login

            } # end for each login

        } # end for each instance

    } # end process

    end {
        if (Test-FunctionInterrupt) { return }
        Write-Message -Message "Finished changing login(s)" -Level Verbose
    }


}