tests/New-DbaLogin.Tests.ps1

$CommandName = $MyInvocation.MyCommand.Name.Replace(".Tests.ps1", "")
Write-Host -Object "Running $PSCommandpath" -ForegroundColor Cyan
. "$PSScriptRoot\constants.ps1"
. "$PSScriptRoot\..\internal\functions\Connect-SqlInstance.ps1"
. "$PSScriptRoot\..\internal\functions\Get-PasswordHash.ps1"
. "$PSScriptRoot\..\internal\functions\Convert-HexStringToByte.ps1"

Describe "$commandname Integration Tests" -Tags "IntegrationTests" {

    $credLogin = 'credologino'
    $certificateName = 'DBAToolsPesterlogincertificate'
    $password = 'MyV3ry$ecur3P@ssw0rd'
    $securePassword = ConvertTo-SecureString $password -AsPlainText -Force
    $sid = '0xDBA700131337C0D30123456789ABCDEF'
    $server1 = Connect-SqlInstance -SqlInstance $script:instance1
    $server2 = Connect-SqlInstance -SqlInstance $script:instance2
    $servers = @($server1, $server2)
    $computerName = $server1.NetName
    $winLogin = "$computerName\$credLogin"
    $logins = "claudio", "port", "tester", "certifico", $winLogin

    #cleanup
    foreach ($instance in $servers) {
        foreach ($login in $logins) {
            if ($l = Get-DbaLogin -SqlInstance $instance -Login $login) {
                $results = $instance.Query("IF EXISTS (SELECT * FROM sys.server_principals WHERE name = '$login') EXEC sp_who '$login'")
                foreach ($spid in $results.spid) {
                    $null = $instance.Query("kill $spid")
                }
                if ($c = $l.EnumCredentials()) {
                    $l.DropCredential($c)
                }
                $l.Drop()
            }
        }
    }

    #create Windows login
    $computer = [ADSI]"WinNT://$computerName"
    try {
        $user = [ADSI]"WinNT://$computerName/$credLogin,user"
        if ($user.Name -eq $credLogin) {
            $computer.Delete('User', $credLogin)
        }
    }
    catch {<#User does not exist#>}

    $user = $computer.Create("user", $credLogin)
    $user.SetPassword($password)
    $user.SetInfo()

    #create credential
    $null = New-DbaCredential -SqlInstance $server1 -Name $credLogin -CredentialIdentity $credLogin -Password $securePassword -Force

    #create master key if not exists
    if (!($mkey = Get-DbaDatabaseMasterKey -SqlInstance $server1 -Database master)) {
        $null = New-DbaDatabaseMasterKey -SqlInstance $server1 -Database master -Password $securePassword -Confirm:$false
    }

    #create certificate
    if ($crt = $server1.Databases['master'].Certificates[$certificateName]) {
        $crt.Drop()
    }
    $null = New-DbaDbCertificate $server1 -Name $certificateName -Password $null

    Context "Create new logins" {
        It "Should be created successfully - Hashed password" {
            $results = New-DbaLogin -SqlInstance $server1 -Login tester -HashedPassword (Get-PasswordHash $securePassword $server1.VersionMajor)
            $results.Name | Should Be "tester"
            $results.DefaultDatabase | Should be 'master'
            $results.IsDisabled | Should be $false
            $results.PasswordExpirationEnabled | Should be $false
            $results.PasswordPolicyEnforced | Should be $false
            $results.LoginType | Should be 'SqlLogin'
        }
        It "Should be created successfully - password, credential and a custom sid " {
            $results = New-DbaLogin -SqlInstance $server1 -Login claudio -Password $securePassword -Sid $sid -MapToCredential $credLogin
            $results.Name | Should Be "claudio"
            $results.EnumCredentials() | Should be $credLogin
            $results.DefaultDatabase | Should be 'master'
            $results.IsDisabled | Should be $false
            $results.PasswordExpirationEnabled | Should be $false
            $results.PasswordPolicyEnforced | Should be $false
            $results.Sid | Should be (Convert-HexStringToByte $sid)
            $results.LoginType | Should be 'SqlLogin'
        }
        It "Should be created successfully - password and all the flags" {
            $results = New-DbaLogin -SqlInstance $server1 -Login port -Password $securePassword -PasswordPolicy -PasswordExpiration -DefaultDatabase tempdb -Disabled -Language Nederlands
            $results.Name | Should Be "port"
            $results.Language | Should Be 'Nederlands'
            $results.EnumCredentials() | Should be $null
            $results.DefaultDatabase | Should be 'tempdb'
            $results.IsDisabled | Should be $true
            $results.PasswordExpirationEnabled | Should be $true
            $results.PasswordPolicyEnforced | Should be $true
            $results.LoginType | Should be 'SqlLogin'
        }
        It "Should be created successfully - Windows login" {
            $results = New-DbaLogin -SqlInstance $server1 -Login $winLogin
            $results.Name | Should Be "$winLogin"
            $results.DefaultDatabase | Should be 'master'
            $results.IsDisabled | Should be $false
            $results.LoginType | Should be 'WindowsUser'
        }
        It "Should be created successfully - certificate" {
            $results = New-DbaLogin -SqlInstance $server1 -Login certifico -MapToCertificate $certificateName
            $results.Name | Should Be "certifico"
            $results.DefaultDatabase | Should be 'master'
            $results.IsDisabled | Should be $false
            $results.LoginType | Should be 'Certificate'
        }

        It "Should be copied successfully" {
            $results = Get-DbaLogin -SqlInstance $server1 -Login tester | New-DbaLogin -SqlInstance $server2 -Disabled:$false -Force
            $results.Name | Should Be "tester"

            $results = Get-DbaLogin -SqlInstance $server1 -Login claudio, port | New-DbaLogin -SqlInstance $server2 -Force -PasswordPolicy -PasswordExpiration -DefaultDatabase tempdb -Disabled -Language Nederlands -NewSid -LoginRenameHashtable @{claudio = 'port'; port = 'claudio'} -MapToCredential $null
            $results.Name | Should Be @("port", "claudio")

            $results = Get-DbaLogin -SqlInstance $server1 -Login tester | New-DbaLogin -SqlInstance $server1 -LoginRenameHashtable @{tester = 'port'} -Force -NewSid
            $results.Name | Should Be "port"
        }

        It "Should retain its same properties" {

            $login1 = Get-Dbalogin -SqlInstance $script:instance1 -login tester
            $login2 = Get-Dbalogin -SqlInstance $script:instance2 -login tester

            $login2 | Should Not BeNullOrEmpty

            # Compare values
            $login1.Name | Should Be $login2.Name
            $login1.Language | Should Be $login2.Language
            $login1.EnumCredentials() | Should be $login2.EnumCredentials()
            $login1.DefaultDatabase | Should be $login2.DefaultDatabase
            $login1.IsDisabled | Should be $login2.IsDisabled
            $login1.PasswordExpirationEnabled | Should be $login2.PasswordExpirationEnabled
            $login1.PasswordPolicyEnforced | Should be $login2.PasswordPolicyEnforced
            $login1.Sid | Should be $login2.Sid
        }

        It "Should not have same properties because of the overrides" {

            $login1 = Get-Dbalogin -SqlInstance $script:instance1 -login claudio
            $login2 = Get-Dbalogin -SqlInstance $script:instance2 -login port

            $login2 | Should Not BeNullOrEmpty

            # Compare values
            $login1.Language | Should Not Be $login2.Language
            $login1.EnumCredentials() | Should Not Be $login2.EnumCredentials()
            $login1.DefaultDatabase | Should Not be $login2.DefaultDatabase
            $login1.IsDisabled | Should Not be $login2.IsDisabled
            $login1.PasswordExpirationEnabled | Should Not be $login2.PasswordExpirationEnabled
            $login1.PasswordPolicyEnforced | Should Not be $login2.PasswordPolicyEnforced
            $login1.Sid | Should Not be $login2.Sid
        }
    }
    Context "Connect with a new login" {
        It "Should login with newly created Sql Login, get instance name and kill the process" {
            $cred = New-Object System.Management.Automation.PSCredential ("tester", $securePassword)
            $s = Connect-SqlInstance -SqlInstance $script:instance1 -SqlCredential $cred
            $s.Name | Should Be $script:instance1
            $results = $server1.Query("IF EXISTS (SELECT * FROM sys.server_principals WHERE name = '$($cred.UserName)') EXEC sp_who '$($cred.UserName)'")
            $results | Should Not BeNullOrEmpty
            foreach ($spid in $results.spid) {
                { Invoke-SqlCmd2 -ServerInstance $script:instance1 -Query "kill $spid" -ErrorAction Stop} | Should Not Throw
            }
        }
    }

    Context "No overwrite" {
        $null = Get-DbaLogin -SqlInstance $server1 -Login tester | New-DbaLogin -SqlInstance $server2 -WarningVariable warning 3>&1
        It "Should not attempt overwrite" {
            $warning | Should Match "Login tester already exists"
        }
    }

    foreach ($instance in $servers) {
        foreach ($login in $logins) {
            if ($l = Get-DbaLogin -SqlInstance $instance -Login $login) {
                $results = $instance.Query("IF EXISTS (SELECT * FROM sys.server_principals WHERE name = '$login') EXEC sp_who '$login'")
                foreach ($spid in $results.spid) {
                    $null = $instance.Query("kill $spid")
                }
                if ($c = $l.EnumCredentials()) {
                    $l.DropCredential($c)
                }
                $l.Drop()
            }
        }
    }

    $computer.Delete('User', $credLogin)
    $server1.Credentials[$credLogin].Drop()
    $server1.Databases['master'].Certificates[$certificateName].Drop()
    if (!$mkey) {
        $null = Remove-DbaDatabaseMasterKey -SqlInstance $script:instance1 -Database master -Confirm:$false
    }
}