Connect to Sql Server under a different login context

Here’s how to connect to sql server under a different windows login context. If you pass in the login name as ‘domain\loginname’ it will indeed fail. Only pass in the login name.

 #omit the domain name in the login, or it will fail
$cred = Get-Credential -Message "Enter your login name" -UserName $env:USERNAME

try{

    $srv = New-Object Microsoft.sqlServer.Management.smo.Server "ServerName"

    $srv.ConnectionContext.LoginSecure = $true
    $srv.ConnectionContext.ConnectAsUser = $true

    $srv.ConnectionContext.ConnectAsUserName = $cred.username 
    $srv.ConnectionContext.ConnectAsUserPassword = $cred.GetNetworkCredential().Password 
    $srv.ConnectionContext.Connect() 
    $srv.ConnectionContext.ExecuteWithResults("SELECT SUSER_SNAME() AS CurrentUser").Tables[0] | ft -AutoSize
}
catch{
    $_ | fl -Force
}
finally{
    $srv.ConnectionContext.Disconnect();
}