Add-SqlLogin

Creates a Login object in an instance of SQL Server.

Syntax

Add-SqlLogin
   [-LoginName <String>]
   -LoginType <LoginType>
   [-DefaultDatabase <String>]
   [-EnforcePasswordPolicy]
   [-EnforcePasswordExpiration]
   [-MustChangePasswordAtNextLogin]
   [-Certificate <String>]
   [-AsymmetricKey <String>]
   [-CredentialName <String>]
   [-LoginPSCredential <PSCredential>]
   [-Enable]
   [-GrantConnectSql]
   [[-Path] <String>]
   [-Script]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]
Add-SqlLogin
   [-LoginName <String>]
   -LoginType <LoginType>
   [-DefaultDatabase <String>]
   [-EnforcePasswordPolicy]
   [-EnforcePasswordExpiration]
   [-MustChangePasswordAtNextLogin]
   [-Certificate <String>]
   [-AsymmetricKey <String>]
   [-CredentialName <String>]
   [-LoginPSCredential <PSCredential>]
   [-Enable]
   [-GrantConnectSql]
   [[-InputObject] <Server>]
   [-Script]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]
Add-SqlLogin
   [-LoginName <String>]
   -LoginType <LoginType>
   [-DefaultDatabase <String>]
   [-EnforcePasswordPolicy]
   [-EnforcePasswordExpiration]
   [-MustChangePasswordAtNextLogin]
   [-Certificate <String>]
   [-AsymmetricKey <String>]
   [-CredentialName <String>]
   [-LoginPSCredential <PSCredential>]
   [-Enable]
   [-GrantConnectSql]
   [[-ServerInstance] <String[]>]
   [-Credential <PSCredential>]
   [-ConnectionTimeout <Int32>]
   [-Script]
   [-AccessToken <PSObject>]
   [-TrustServerCertificate]
   [-HostNameInCertificate <String>]
   [-Encrypt <String>]
   [-ProgressAction <ActionPreference>]
   [<CommonParameters>]

Description

The Add-SqlLogin cmdlet creates a Login object in an instance of SQL Server.

Examples

Example 1: Create an SqlLogin type

PS C:\> Add-SqlLogin -ServerInstance "MyServerInstance" -LoginName "MyLogin" -LoginType "SqlLogin" -DefaultDatabase "OtherDatabase"

Name                                          Login Type    Created
----                                          ----------    -------
MyLogin                                       SqlLogin      8/11/2016 3:19 PM

This command creates a Login object that is named 'MyLogin' of the type SqlLogin. The command specifies its default database as 'OtherDatabase' in the server instance named 'MyServerInstance'. This command prompts you for a password for the Login.

Example 2: Create an asymmetric key type

PS C:\> Add-SqlLogin -ServerInstance "MyServerInstance" -LoginName "MyLogin" -LoginType "AsymmetricKey" -AsymmetricKey "MyKey" -CredentialName "MyCredential"

Name                                          Login Type    Created
----                                          ----------    -------
MyLogin                                       AsymmetricKey 8/11/2016 4:08 PM

This command creates a Login object that is named MyLogin of the type AsymmetricKey. It specifies an asymmetric key that is named MyKey. Also it maps the credential called MyCredential to the new Login object. The command operates in the server instance named MyServerInstance.

Parameters

-AccessToken

The access token used to authenticate to SQL Server, as an alternative to user/password or Windows Authentication.

This can be used, for example, to connect to SQL Azure DB and SQL Azure Managed Instance using a Service Principal or a Managed Identity.

The parameter to use can be either a string representing the token or a PSAccessToken object as returned by running Get-AzAccessToken -ResourceUrl https://database.windows.net.

This parameter is new in v22 of the module.

Type:PSObject
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-AsymmetricKey

Specify the name of the asymmetric key for the Login object. If the LoginType parameter has the value AsymmetricKey, specify an asymmetric key.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Certificate

Specify the name of the certificate for the Login object. If LoginType has the value Certificate, specify a certificate.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ConnectionTimeout

Specifies the number of seconds to wait for a server connection before a time-out failure. The time-out value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.

Type:Int32
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Credential

Specifies a PSCredential object for the connection to SQL Server. To obtain a credential object, use the Get-Credential cmdlet. For more information, type Get-Help Get-Credential.

Type:PSCredential
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-CredentialName

Specify the name of the credential for the Login object.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-DefaultDatabase

Specify the default database for the Login object. The default value is master.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Enable

Indicates that the Login object is enabled. By default, Login objects are disabled.

WindowsGroup type objects are always enabled. This parameter does not affect them.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Encrypt

The encryption type to use when connecting to SQL Server.

This value maps to the Encrypt property SqlConnectionEncryptOption on the SqlConnection object of the Microsoft.Data.SqlClient driver.

In v22 of the module, the default is Optional (for compatibility with v21). In v23+ of the module, the default value will be 'Mandatory', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:String
Accepted values:Mandatory, Optional, Strict
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-EnforcePasswordExpiration

Indicates that the password expiration policy is enforced for the Login object. This parameter applies only SqlLogin type objects. This parameter implies the EnforcePasswordPolicy parameter. You do not have to specify both.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-EnforcePasswordPolicy

Indicates that the password policy is enforced for the Login object. This parameter applies only SqlLogin type objects.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-GrantConnectSql

Indicates that the Login object is not denied permissions to connect to the database engine. By default, Login objects are denied permissions to connect to the database engine, unless the target server is SQL Azure.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-HostNameInCertificate

The host name to be used in validating the SQL Server TLS/SSL certificate. You must pass this parameter if your SQL Server instance is enabled for Force Encryption and you want to connect to an instance using hostname/shortname. If this parameter is omitted then passing the Fully Qualified Domain Name (FQDN) to -ServerInstance is necessary to connect to a SQL Server instance enabled for Force Encryption.

This parameter is new in v22 of the module.

Type:String
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-InputObject

Specifies an SQL Server Management Objects (SMO) object the SQL Server on which this cmdlet operates.

Type:Server
Position:1
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-LoginName

Specifies a name for the Login object. The case sensitivity is the same as that of the instance of SQL Server.

Type:String
Aliases:Name
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-LoginPSCredential

Specifies a PSCredential object that allows the Login object to provide name and password without a prompt.

Type:PSCredential
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-LoginType

Specifies the type of the Login object as a Microsoft.SqlServer.Management.Smo.LoginType value. The acceptable values for this parameter are:

  • AsymmetricKey
  • Certificate
  • SqlLogin
  • WindowsGroup
  • WindowsUser

At this time, the cmdlet does not support ExternalUser or ExternalGroup.

Type:LoginType
Accepted values:WindowsUser, WindowsGroup, SqlLogin, Certificate, AsymmetricKey, ExternalUser, ExternalGroup
Position:Named
Default value:None
Required:True
Accept pipeline input:False
Accept wildcard characters:False

-MustChangePasswordAtNextLogin

Indicates that the user must change the password at the next login. This parameter applies only SqlLogin type objects. This parameter implies the EnforcePasswordExpiration parameter. You do not have to specify both.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Path

Specifies the path of the SQL Server on which this cmdlet runs the operation. The default value is the current working directory.

Type:String
Position:1
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ProgressAction

Determines how PowerShell responds to progress updates generated by a script, cmdlet, or provider, such as the progress bars generated by the Write-Progress cmdlet. The Write-Progress cmdlet creates progress bars that show a command's status.

Type:ActionPreference
Aliases:proga
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-Script

Indicates that this cmdlet returns a Transact-SQL script that performs the task that this cmdlet performs.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

-ServerInstance

Specifies the name of an instance of SQL Server. For the default instance, specify the computer name. For named instances, use the format ComputerName\InstanceName.

Type:String[]
Position:1
Default value:None
Required:False
Accept pipeline input:True
Accept wildcard characters:False

-TrustServerCertificate

Indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

In v22 of the module, the default is $true (for compatibility with v21). In v23+ of the module, the default value will be '$false', which may create a breaking change for existing scripts.

This parameter is new in v22 of the module.

Type:SwitchParameter
Position:Named
Default value:None
Required:False
Accept pipeline input:False
Accept wildcard characters:False

Inputs

Microsoft.SqlServer.Management.Smo.Server

System.String[]

Outputs

System.Object