< Back
Set-DbaLogin
Post
NAME Set-DbaLogin
SYNOPSIS
Set-DbaLogin makes it possible to make changes to one or more logins.
SYNTAX
Set-DbaLogin [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [[-SqlCredential]
<Pscredential>] [[-Login] <System.String[]>] [[-SecurePassword] <System.Object>] [[-DefaultDatabase] <String>]
[-Unlock <Switch>] [-MustChange <Switch>] [[-NewName] <String>] [-Disable <Switch>] [-Enable <Switch>] [-DenyLogin
<Switch>] [-GrantLogin <Switch>] [-PasswordPolicyEnforced <Switch>] [[-AddRole] <System.String[]>] [[-RemoveRole]
<System.String[]>] [[-InputObject] <Microsoft.SqlServer.Management.Smo.Login[]>] [-EnableException <Switch>]
[<CommonParameters>]
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.
PARAMETERS
-AddRole [<System.String[]>]
Add one or more server roles to the login
The following roles can be used "bulkadmin", "dbcreator", "diskadmin", "processadmin", "public",
"securityadmin", "serveradmin", "setupadmin", "sysadmin".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DefaultDatabase [<String>]
Default database for the login
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DenyLogin [<Switch>]
Deny access to SQL Server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Disable [<Switch>]
Disable the login
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Enable [<Switch>]
Enable the login
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-GrantLogin [<Switch>]
Grant access to SQL Server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<Microsoft.SqlServer.Management.Smo.Login[]>]
Allows logins to be piped in from Get-DbaLogin
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Login [<System.String[]>]
The login that needs to be changed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MustChange [<Switch>]
Does the user need to change his/her password. This will only be used in conjunction with the -SecurePassword
parameter.
The default is false.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NewName [<String>]
The new name for the login.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PasswordPolicyEnforced [<Switch>]
Should the password policy be enforced.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-RemoveRole [<System.String[]>]
Remove one or more server roles to the login
The following roles can be used "bulkadmin", "dbcreator", "diskadmin", "processadmin", "public",
"securityadmin", "serveradmin", "setupadmin", "sysadmin".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SecurePassword [<System.Object>]
The new password for the login This can be either a credential or a secure string.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. You must have sysadmin access and server version must be SQL
Server version 2000 or greater.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Unlock [<Switch>]
Switch to unlock an account. This will only be used in conjunction with the -SecurePassword parameter.
The default is false.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Login
Author: Sander Stad (@sqlstad), sqlstad.nl
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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
RELATED LINKS
https://dbatools.io/Set-DbaLogin
SYNOPSIS
Set-DbaLogin makes it possible to make changes to one or more logins.
SYNTAX
Set-DbaLogin [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [[-SqlCredential]
<Pscredential>] [[-Login] <System.String[]>] [[-SecurePassword] <System.Object>] [[-DefaultDatabase] <String>]
[-Unlock <Switch>] [-MustChange <Switch>] [[-NewName] <String>] [-Disable <Switch>] [-Enable <Switch>] [-DenyLogin
<Switch>] [-GrantLogin <Switch>] [-PasswordPolicyEnforced <Switch>] [[-AddRole] <System.String[]>] [[-RemoveRole]
<System.String[]>] [[-InputObject] <Microsoft.SqlServer.Management.Smo.Login[]>] [-EnableException <Switch>]
[<CommonParameters>]
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.
PARAMETERS
-AddRole [<System.String[]>]
Add one or more server roles to the login
The following roles can be used "bulkadmin", "dbcreator", "diskadmin", "processadmin", "public",
"securityadmin", "serveradmin", "setupadmin", "sysadmin".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DefaultDatabase [<String>]
Default database for the login
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DenyLogin [<Switch>]
Deny access to SQL Server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Disable [<Switch>]
Disable the login
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Enable [<Switch>]
Enable the login
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-GrantLogin [<Switch>]
Grant access to SQL Server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InputObject [<Microsoft.SqlServer.Management.Smo.Login[]>]
Allows logins to be piped in from Get-DbaLogin
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Login [<System.String[]>]
The login that needs to be changed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-MustChange [<Switch>]
Does the user need to change his/her password. This will only be used in conjunction with the -SecurePassword
parameter.
The default is false.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NewName [<String>]
The new name for the login.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-PasswordPolicyEnforced [<Switch>]
Should the password policy be enforced.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-RemoveRole [<System.String[]>]
Remove one or more server roles to the login
The following roles can be used "bulkadmin", "dbcreator", "diskadmin", "processadmin", "public",
"securityadmin", "serveradmin", "setupadmin", "sysadmin".
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SecurePassword [<System.Object>]
The new password for the login This can be either a credential or a secure string.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. You must have sysadmin access and server version must be SQL
Server version 2000 or greater.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Unlock [<Switch>]
Switch to unlock an account. This will only be used in conjunction with the -SecurePassword parameter.
The default is false.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
Tags: Login
Author: Sander Stad (@sqlstad), sqlstad.nl
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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
RELATED LINKS
https://dbatools.io/Set-DbaLogin