commands

^

Set-DbaLogin

Author Sander Stad (@sqlstad), sqlstad.nl
Availability Windows, Linux, macOS

 

Synopsis

Set-DbaLogin makes it possible to make changes to one or more logins. SQL Azure DB is not supported.

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.

Syntax

Set-DbaLogin
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Login] <String[]>]
    [[-SecurePassword] <Object>]
    [[-DefaultDatabase] <String>]
    [-Unlock]
    [-MustChange]
    [[-NewName] <String>]
    [-Disable]
    [-Enable]
    [-DenyLogin]
    [-GrantLogin]
    [-PasswordPolicyEnforced]
    [-PasswordExpirationEnabled]
    [[-AddRole] <String[]>]
    [[-RemoveRole] <String[]>]
    [[-InputObject] <Login[]>]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> $SecurePassword = ConvertTo-SecureString "PlainTextPassword" -AsPlainText -Force
PS C:\> $cred = New-Object System.Management.Automation.PSCredential ("username", $SecurePassword)
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -SecurePassword $cred -Unlock -MustChange

Set the new password for login1 using a credential, unlock the account and set the option
that the user must change password at next logon.

Example: 2
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -Enable

Enable the login

Example: 3
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1, login2, login3, login4 -Enable

Enable multiple logins

Example: 4
PS C:\> Set-DbaLogin -SqlInstance sql1, sql2, sql3 -Login login1, login2, login3, login4 -Enable

Enable multiple logins on multiple instances

Example: 5
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -Disable

Disable the login

Example: 6
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -DenyLogin

Deny the login to connect to the instance

Example: 7
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -GrantLogin

Grant the login to connect to the instance

Example: 8
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -PasswordPolicyEnforced

Enforces the password policy on a login

Example: 9
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -PasswordPolicyEnforced:$false

Disables enforcement of the password policy on a login

Example: 10
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login test -AddRole serveradmin

Add the server role "serveradmin" to the login

Example: 11
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login test -RemoveRole bulkadmin

Remove the server role "bulkadmin" to the login

Example: 12
PS C:\> $login = Get-DbaLogin -SqlInstance sql1 -Login test
PS C:\> $login | Set-DbaLogin -Disable

Disable the login from the pipeline

Example: 13
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -DefaultDatabase master

Set the default database to master on a login

Example: 14
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -Unlock -Force

Unlocks the login1 on the sql1 instance using the technique described at https://www.mssqltips.com/sqlservertip/2758/how-to-unlock-a-sql-login-without-resetting-the-password/

Optional Parameters

-SqlInstance

The target SQL Server instance or instances. You must have sysadmin access and server version must be SQL Server version 2000 or greater.

Alias
Required False
Pipeline false
Default Value
-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential). Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported. For MFA support, please use Connect-DbaInstance.

Alias
Required False
Pipeline false
Default Value
-Login

The login that needs to be changed

Alias
Required False
Pipeline false
Default Value
-SecurePassword

The new password for the login This can be either a credential or a secure string.

Alias Password
Required False
Pipeline false
Default Value
-DefaultDatabase

Default database for the login

Alias DefaultDB
Required False
Pipeline false
Default Value
-Unlock

Switch to unlock an account. This can be used in conjunction with the -SecurePassword or -Force parameters. The default is false.

Alias
Required False
Pipeline false
Default Value False
-MustChange

Does the user need to change his/her password. This will only be used in conjunction with the -SecurePassword parameter. It is required that the login have both PasswordPolicyEnforced (check_policy) and PasswordExpirationEnabled (check_expiration) enabled for the login. See the Microsoft documentation for ALTER LOGIN for more details. The default is false.

Alias
Required False
Pipeline false
Default Value False
-NewName

The new name for the login.

Alias
Required False
Pipeline false
Default Value
-Disable

Disable the login

Alias
Required False
Pipeline false
Default Value False
-Enable

Enable the login

Alias
Required False
Pipeline false
Default Value False
-DenyLogin

Deny access to SQL Server

Alias
Required False
Pipeline false
Default Value False
-GrantLogin

Grant access to SQL Server

Alias
Required False
Pipeline false
Default Value False
-PasswordPolicyEnforced

Enable the password policy on the login (check_policy = ON). This option must be enabled in order for -PasswordExpirationEnabled to be used.

Alias
Required False
Pipeline false
Default Value False
-PasswordExpirationEnabled

Enable the password expiration check on the login (check_expiration = ON). In order to enable this option the PasswordPolicyEnforced (check_policy) must also be enabled for the login.

Alias
Required False
Pipeline false
Default Value False
-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".

Alias
Required False
Pipeline false
Default Value
Accepted Values bulkadmin,dbcreator,diskadmin,processadmin,public,securityadmin,serveradmin,setupadmin,sysadmin
-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".

Alias
Required False
Pipeline false
Default Value
Accepted Values bulkadmin,dbcreator,diskadmin,processadmin,public,securityadmin,serveradmin,setupadmin,sysadmin
-InputObject

Allows logins to be piped in from Get-DbaLogin

Alias
Required False
Pipeline true (ByValue)
Default Value
-Force

This switch is used with -Unlock to unlock a login without providing a password. This command will temporarily disable and enable the policy settings as described at https://www.mssqltips.com/sqlservertip/2758/how-to-unlock-a-sql-login-without-resetting-the-password/.

Alias
Required False
Pipeline false
Default Value False
-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.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.

Alias cf
Required False
Pipeline false
Default Value

 

Want to see the source code for this command? Check out Set-DbaLogin on GitHub.
Want to see the Bill Of Health for this command? Check out Set-DbaLogin.