New-Login

DBSystems: Creates a new Login in a SQL Server instance

Script

#Requires -Version 5.0
#Requires -Modules SQLServer

[CmdLetBinding()]
Param(
    [Parameter(Mandatory = $true)]   
    [pscredential]$LoginCredential,
    [Parameter(Mandatory = $true)]   
    [ValidateSet('WindowsUser', 'WindowsGroup', 'SqlLogin', 'Certificate', 'AsymmetricKey')]
    [string]$LoginType = "SqlLogin",
    [Parameter(Mandatory = $true)]   
    [string]$ServerInstance,    
    [pscredential]$ServerCredential,
    [string]$DefaultDatabase,
    [switch]$Enable,
    [switch]$EnforcePasswordExpiration,
    [switch]$EnforcePasswordPolicy,
    [switch]$MustChangePasswordAtNextLogin,
    [switch]$GrantConnectSql,
    [string]$AsymmetricKey,
    [string]$Certificate,
    [SecureString]$CredentialName,
    [int]$ConnectionTimeout = 30
)

function Get-SqlServerInstanceInternal {
    [CmdLetBinding()]
    Param(
        [Parameter(Mandatory = $true)]   
        [string]$ServerInstance,    
        [pscredential]$ServerCredential,
        [int]$ConnectionTimeout = 30
    )
    try {
        [hashtable]$cmdArgs = @{
            'ErrorAction' = 'Stop'
            'Confirm' = $false
            'ServerInstance' = $ServerInstance
            'ConnectionTimeout' = $ConnectionTimeout
        }
        if ($null -ne $ServerCredential) {
            $cmdArgs.Add('Credential', $ServerCredential)
        }
        return Get-SqlInstance @cmdArgs
    } catch {
        throw
    }
}

Import-Module SQLServer

try {
    $Properties = @('Name','Status','LoginType','Language','IsLocked','IsDisabled','IsPasswordExpired','MustChangePassword','PasswordExpirationEnabled','HasAccess','State')
    $instance = Get-SqlServerInstanceInternal -ServerInstance $ServerInstance -ServerCredential $ServerCredential -ConnectionTimeout $ConnectionTimeout

    [hashtable]$cmdArgs = @{
        'ErrorAction' = 'Stop'
        'LoginType' = $LoginType
        'InputObject' = $instance
        'Enable' = $Enable.ToBool()
        "LoginPSCredential" = $LoginCredential
        'GrantConnectSql' = $GrantConnectSql.ToBool()
    }
    
    if ($LoginType -eq "SqlLogin") {
        $cmdArgs.Add("EnforcePasswordExpiration", $EnforcePasswordExpiration.ToBool())
        $cmdArgs.Add("EnforcePasswordPolicy", $EnforcePasswordPolicy.ToBool())
        $cmdArgs.Add("MustChangePasswordAtNextLogin", $MustChangePasswordAtNextLogin.ToBool())
    } 
    
    if (-not [string]::IsNullOrWhiteSpace($DefaultDatabase)) {        
        $cmdArgs.Add("DefaultDatabase", $DefaultDatabase)
    }
    if (-not [string]::IsNullOrWhiteSpace($AsymmetricKey)) {        
        $cmdArgs.Add("AsymmetricKey", $AsymmetricKey)
    }
    if (-not [string]::IsNullOrWhiteSpace($Certificate)) {        
        $cmdArgs.Add("Certificate", $Certificate)
    }
    if ($null -ne $CredentialName) {
        $ptr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($CredentialName)
        try {
            $plainName = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($ptr)
            $cmdArgs.Add("CredentialName", $plainName)
        } finally {
            [System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($ptr)
        }
    }
       
    $result = Add-SqlLogin @cmdArgs | Select-Object $Properties
    Write-Output $result
} catch {
    throw
}

Customize

LoginCredential pscredential

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

LoginType string

Specifies the type of the Login object

ServerInstance string

Specifies the name of the target computer including the instance name, e.g. MyServer\Instance

ServerCredential pscredential

Specifies a PSCredential object for the connection to the SQL Server. ServerCredential is ONLY used for SQL Logins. When you are using Windows Authentication you don't specify -Credential. It is picked up from your current login.

DefaultDatabase string

Specify the default database for the Login object

Enable switch

Off

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

EnforcePasswordExpiration switch

Off

Indicates that the password expiration policy is enforced for the Login object

EnforcePasswordPolicy switch

Off

Indicates that the password policy is enforced for the Login object

MustChangePasswordAtNextLogin switch

Off

Indicates that the user must change the password at the next login

GrantConnectSql switch

Off

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

AsymmetricKey string

Specify the name of the asymmetric key for the Login object

Certificate string

Specify the name of the certificate for the Login object

CredentialName SecureString

Specify the name of the credential for the Login object

ConnectionTimeout int

Specifies the time period to retry the command on the target server