| Author | Sander Stad (@sqlstad), sqlstad.nl |
| Availability | Windows, Linux, macOS |
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.
Set-DbaLogin makes it possible to make changes to one or more logins.
SQL Azure DB is not supported.
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.
Set-DbaLogin
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-Login] <String[]>]
[[-SecurePassword] <Object>]
[[-DefaultDatabase] <String>]
[-Unlock]
[-PasswordMustChange]
[[-NewName] <String>]
[-Disable]
[-Enable]
[-DenyLogin]
[-GrantLogin]
[-PasswordPolicyEnforced]
[-PasswordExpirationEnabled]
[[-AddRole] <String[]>]
[[-RemoveRole] <String[]>]
[[-InputObject] <Login[]>]
[-Force]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> $SecurePassword = (Get-Credential NoUsernameNeeded).Password
PS C:\> $cred = New-Object System.Management.Automation.PSCredential ("username", $SecurePassword)
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -SecurePassword $cred -Unlock -PasswordMustChange
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.
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -Enable
Enable the login
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1, login2, login3, login4 -Enable
Enable multiple logins
PS C:\> Set-DbaLogin -SqlInstance sql1, sql2, sql3 -Login login1, login2, login3, login4 -Enable
Enable multiple logins on multiple instances
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -Disable
Disable the login
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -DenyLogin
Deny the login to connect to the instance
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -GrantLogin
Grant the login to connect to the instance
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -PasswordPolicyEnforced
Enforces the password policy on a login
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -PasswordPolicyEnforced:$false
Disables enforcement of the password policy on a login
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login test -AddRole serveradmin
Add the server role "serveradmin" to the login
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login test -RemoveRole bulkadmin
Remove the server role "bulkadmin" to the login
PS C:\> $login = Get-DbaLogin -SqlInstance sql1 -Login test
PS C:\> $login | Set-DbaLogin -Disable
Disable the login from the pipeline
PS C:\> Set-DbaLogin -SqlInstance sql1 -Login login1 -DefaultDatabase master
Set the default database to master on a login
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/
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 |
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 |
The login that needs to be changed
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
The new password for the login This can be either a credential or a secure string.
| Alias | Password |
| Required | False |
| Pipeline | false |
| Default Value |
Default database for the login
| Alias | DefaultDB |
| Required | False |
| Pipeline | false |
| Default Value |
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 |
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 | MustChange |
| Required | False |
| Pipeline | false |
| Default Value | False |
The new name for the login.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Disable the login
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Enable the login
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Deny access to SQL Server
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Grant access to SQL Server
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
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 |
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 |
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 |
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 |
Allows logins to be piped in from Get-DbaLogin
| Alias | |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
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 |
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 |
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 |
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 |