Logging in to another SQL Server from a PowerShell session

Whenever you run the command SQLPS.EXE on a SQL Server 2008 system, or right-click an object and select "Start PowerShell Here" from the menu, you’re dropped into the environment on that server. But sometimes you want to connect to a different server from there.

My good friend Michiel Wories, the technical lead on the PowerShell project for SQL Server, helped me develop this script to do just that:

   1: # This will connect you to machine X and instance Y using login “sa”
   2: $pwd = Read-Host -Prompt "Enter password" -asSecureString
   3: $cred = New-Object System.Management.Automation.PSCredential -ArgumentList “sa”,$pwd
   4: New-PSDrive “TEST” -PSProvider SqlServer -Root “SQLSERVER:\SQL\X\Y” –Credential $cred

As always, this warning applies to any script you find anywhere, including here.