invoke-sqlmcd with another user

Question

Post reply

invoke-sqlmcd with another user

abdalah.mehdoini

Ten Centuries

Points: 1019

November 28, 2019 at 3:41 pm

#3701935

Hello everyone

Who has an idea how I can run invoke-sqlmcd with a nother user

I try with the credential part but it does not work

thank you for your help

$startTime = (Get-Date)
$username = "A1\amdev"
$password = "ge56hSW7U"
 
$myCredential = New-Object System.Management.Automation.PSCredential($username, (ConvertTo-SecureString $password -AsPlainText -Force))

Start-Process powershell.exe -Credential $myCredential 

IF (!(Get-Module -Name sqlps))
    {
        Write-Host 'Loading SQLPS Module' -ForegroundColor DarkYellow
        Push-Location
        Import-Module sqlps -DisableNameChecking
        Pop-Location
    }
   
$localScriptRoot = "C:\TSQL\Insertion Distind_importation_tampon\"
$Server = "DWH"
$scripts = Get-ChildItem $localScriptRoot | Where-Object {$_.Extension -eq ".sql"} 
  
foreach ($s in $scripts)
    {
        Write-Host "Running Script : " $s.Name -BackgroundColor DarkGreen -ForegroundColor White

        Invoke-Sqlcmd -ServerInstance $Server -InputFile $s    -Querytimeout 0  

    }

    $endTime = (Get-Date)

    $ElapsedTime = (($endTime-$startTime))

'Duration: {0:mm} min {0:ss} sec' -f $ElapsedTime

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99134 More actions · Answer 1

Is there a reason you can't launch the Powershell window as a different user?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ken McKelvey SSCoach Points: 18867 More actions · Answer 2

Try defining $password as:

$password = "ge56hSW7U" | ConvertTo-SecureString -AsPlainText -Force

and then just using the variable when creating the object.

Personally I would be inclined not to have the password in the script but in an encrypted file as detailed here:

https://www.red-gate.com/simple-talk/sysadmin/powershell/powershell-and-secure-strings/

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

is it easier to just put EXECUE AS in your Tsql script?

or is this not where the issue lies?

MVDBA