commands

Clear
^

New-DbaLogin
Author Kirill Kravtsov (@nvarscar)
Availability Windows, Linux, macOS

 

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

Synopsis

Creates a new SQL Server login

Description

Creates a new SQL Server login with provided specifications

Syntax

New-DbaLogin
    [-SqlInstance] <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [[-Login] <String[]>]
    [-InputObject <Object[]>]
    [-LoginRenameHashtable <Hashtable>]
    [[-SecurePassword] <SecureString>]
    [-MapToCredential <String>]
    [-Sid <Object>]
    [-DefaultDatabase <String>]
    [-Language <String>]
    [-PasswordExpirationEnabled]
    [-PasswordPolicyEnforced]
    [-PasswordMustChange]
    [-Disabled]
    [-DenyWindowsLogin]
    [-NewSid]
    [-ExternalProvider]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

New-DbaLogin
    [-SqlInstance] <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Login <String[]>]
    [-InputObject <Object[]>]
    [-LoginRenameHashtable <Hashtable>]
    [-MapToAsymmetricKey <String>]
    [-MapToCredential <String>]
    [-Sid <Object>]
    [-Disabled]
    [-DenyWindowsLogin]
    [-NewSid]
    [-ExternalProvider]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

New-DbaLogin
    [-SqlInstance] <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Login <String[]>]
    [-InputObject <Object[]>]
    [-LoginRenameHashtable <Hashtable>]
    [-MapToCertificate <String>]
    [-MapToCredential <String>]
    [-Sid <Object>]
    [-Disabled]
    [-DenyWindowsLogin]
    [-NewSid]
    [-ExternalProvider]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

New-DbaLogin
    [-SqlInstance] <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Login <String[]>]
    [-InputObject <Object[]>]
    [-LoginRenameHashtable <Hashtable>]
    [-HashedPassword <String>]
    [-MapToCredential <String>]
    [-Sid <Object>]
    [-DefaultDatabase <String>]
    [-Language <String>]
    [-PasswordExpirationEnabled]
    [-PasswordPolicyEnforced]
    [-Disabled]
    [-DenyWindowsLogin]
    [-NewSid]
    [-ExternalProvider]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> New-DbaLogin -SqlInstance Server1,Server2 -Login Newlogin

You will be prompted to securely enter the password for a login [Newlogin]. The login would be created on servers Server1 and Server2 with default parameters.

Example: 2
PS C:\> $securePassword = Read-Host "Input password" -AsSecureString
PS C:\> New-DbaLogin -SqlInstance Server1\sql1 -Login Newlogin -SecurePassword $securePassword -PasswordPolicyEnforced -PasswordExpirationEnabled

Creates a login on Server1\sql1 with a predefined password. The login will have password and expiration policies enforced onto it.

Example: 3
PS C:\> Get-DbaLogin -SqlInstance sql1 -Login Oldlogin | New-DbaLogin -SqlInstance sql1 -LoginRenameHashtable @{Oldlogin = 'Newlogin'} -Force -NewSid -Disabled:$false

Copies a login [Oldlogin] to the same instance sql1 with the same parameters (including password). New login will have a new sid, a new name [Newlogin] and will not be disabled. Existing login
[Newlogin] will be removed prior to creation.

Example: 4
PS C:\> Get-DbaLogin -SqlInstance sql1 -Login Login1,Login2 | New-DbaLogin -SqlInstance sql2 -PasswordPolicyEnforced -PasswordExpirationEnabled -DefaultDatabase tempdb -Disabled

Copies logins [Login1] and [Login2] from instance sql1 to instance sql2, but enforces password and expiration policies for the new logins. New logins will also have a default database set to [tempdb]
and will be created in a disabled state.

Example: 5
PS C:\> New-DbaLogin -SqlInstance sql1 -Login domain\user

Creates a new Windows Authentication backed login on sql1. The login will be part of the public server role.

Example: 6
PS C:\> New-DbaLogin -SqlInstance sql1 -Login domain\user1, domain\user2 -DenyWindowsLogin

Creates two new Windows Authentication backed login on sql1. The logins would be denied from logging in.

Example: 7
PS C:\> New-DbaLogin -SqlInstance sql1 -Login "claudio@********.onmicrosoft.com" -ExternalProvider

Creates a new login named 'claudio@********.onmicrosoft.com' mapped to Azure Active Directory (AAD).

Required Parameters

-SqlInstance

The target SQL Server(s)

Alias
Required True
Pipeline false
Default Value

Optional Parameters

-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 name(s)
Alias Name,LoginName
Required False
Pipeline false
Default Value
-InputObject

Takes the parameters required from a Login object that has been piped into the command
Alias
Required False
Pipeline true (ByValue)
Default Value
-LoginRenameHashtable

Pass a hash table into this parameter to change login names when piping objects into the procedure
Alias Rename
Required False
Pipeline false
Default Value
-SecurePassword

Secure string used to authenticate the Login
Alias Password
Required False
Pipeline false
Default Value
-HashedPassword

Hashed password string used to authenticate the Login
Alias Hash,PasswordHash
Required False
Pipeline false
Default Value
-MapToCertificate

Map the login to a certificate
Alias
Required False
Pipeline false
Default Value
-MapToAsymmetricKey

Map the login to an asymmetric key
Alias
Required False
Pipeline false
Default Value
-MapToCredential

Map the login to a credential
Alias
Required False
Pipeline false
Default Value
-Sid

Provide an explicit Sid that should be used when creating the account. Can be [byte[]] or hex [string] ('0xFFFF...')
Alias
Required False
Pipeline false
Default Value
-DefaultDatabase

Default database for the login
Alias DefaultDB
Required False
Pipeline false
Default Value
-Language

Login's default language
Alias
Required False
Pipeline false
Default Value
-PasswordExpirationEnabled

Enforces password expiration policy. Requires PasswordPolicyEnforced to be enabled. Can be $true or $false(default)
Alias Expiration,CheckExpiration
Required False
Pipeline false
Default Value False
-PasswordPolicyEnforced

Enforces password complexity policy. Can be $true or $false(default)
Alias Policy,CheckPolicy
Required False
Pipeline false
Default Value False
-PasswordMustChange

Enforces user must change password at next login.
When specified will enforce PasswordExpirationEnabled and PasswordPolicyEnforced as they are required for the must change.
Alias MustChange
Required False
Pipeline false
Default Value False
-Disabled

Create the login in a disabled state
Alias Disable
Required False
Pipeline false
Default Value False
-DenyWindowsLogin

Create the login and deny Windows login ability
Alias
Required False
Pipeline false
Default Value False
-NewSid

Ignore sids from the piped login object to generate new sids on the server. Useful when copying login onto the same server
Alias
Required False
Pipeline false
Default Value False
-ExternalProvider

Specifies that the login is for Azure AD Authentication.
Equivalent to T-SQL: 'CREATE LOGIN [claudio@********.onmicrosoft.com] FROM EXTERNAL PROVIDER`
Alias
Required False
Pipeline false
Default Value False
-Force

If login exists, drop and recreate
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

Shows what would happen if the command were to run. No actions are actually performed
Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts you for confirmation before executing any changing operations within the command
Alias cf
Required False
Pipeline false
Default Value