CODEWITHSUNDEEP
sql-serverpowershell
A person
A person

Reputation: 63

Error starting MSSQL as a different user via powershell

There's a database at work that I need to get into often, but you need to run MSSQL2005 as a different windows account to get into it. I got tired of looking up the password, right clicking the shortcut and telling it to run as a different user, so I made a powershell script to start MSSQL as the required user. It successfully starts up the program and shows you the tables in the DB, but when you open a table or try to run a query, it errors out with the following:

"Microsoft SQL Server Management Studio - The system cannot find the file specified. (SQLEditors)"

If I do the normal way (right click the shortcut and run as a different user), it works fine. The issue is only when I open it from the powershell script.

I've found the powershell script works when that account is logged onto the node, but the second it logs off it dies again. Another thing I noticed was that the first time I loaded MSSQL with the powershell script, it asked for the DB name even though it was previously saved as a recent connection via the working way. It's like it's not finding the right configs or something... I've used this exact script in real projects in the past so it's known to work, so I'm assuming the problem has something to do with MSSQL2005.

The script:

function Get-ScriptDirectory
    {
    $Invocation = (Get-Variable MyInvocation -Scope 1).Value;
    if($Invocation.PSScriptRoot)
    {
        $Invocation.PSScriptRoot;
    }
    Elseif($Invocation.MyCommand.Path)
    {
        Split-Path $Invocation.MyCommand.Path
    }
    else
    {
        $Invocation.InvocationName.Substring(0,$Invocation.InvocationName.LastIndexOf("\"));
    }
}
$curDir = Get-ScriptDirectory
$encpwd = Get-Content $curDir\password.bin
$passwd = ConvertTo-SecureString $encpwd

$exeLoc = "D:\Program Files\Microsoft SQL Server (x86)\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"
$cred = new-object System.Management.Automation.PSCredential '[username]' , $passwd
Start-Process $exeLoc -Cred $cred

Edit: Svein Fidjestøl's comment fixed the issue. The Start-Process needed a "-LoadUserProfile" at the end of it.

Upvotes: 0

Views: 376

Answers (1)

Svein Fidjestøl
Svein Fidjestøl

Reputation: 3206

A lot of Windows applications and services have an implicit requirement that the user profile must be loaded. This seems to be the case for SQL Server 2005.

In PowerShell, to load the user profile when invoking the process, pass the -LoadUserProfile parameter to the Start-Process cmdlet. So in your example, you would invoke SQL Server like this

Start-Process $exeLoc -Cred $cred -LoadUserProfile

Upvotes: 1

Related Questions