Call SQL Agent job using different account to one executing powershell

Question

Call SQL Agent job using different account to one executing powershell

Dave

Old Hand

Points: 350
More actions
June 8, 2023 at 4:14 pm

#4207166

Hi Folks
trying the below but having zero luck and was wondering if anybody had any idea?
$SQLServer = "DEV"
$db = "master"
$q = "exec msdb.dbo.sp_start_job @job_name = 'JOB'"
$username = ''
$password = '' | ConvertTo-SecureString -AsPlainText -Force
$credentials = New-Object System.Management.Automation.PSCredential($username, $password)
$powerShellExe = 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe'
Start-Process -FilePath $powerShellExe -ArgumentList "Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db -Query $q" -Credential $credentials
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

What happens when you try this ... is there an error?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Dave Old Hand Points: 350 More actions · Answer 2

Hi Phil,

Opens a new blank window and then closes that and thats pretty much it, no errors or feedback at all

cheers

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

You could put the statement 'pause' at the end of your script. It will prevent the window auto-closing and maybe there will be some sort of informational message there.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Dave Old Hand Points: 350 More actions · Answer 4

Hi Phil

added in "Start-Sleep -Seconds 60" for a minutes pause but the window still just flicks open and closes as before, there appears to be some text in the window but as it closes so quickly its impossible to see the output

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Sounds like there may be an error occurring which is forcing the window to close despite the Sleep.

Try this instead:

Read-Host -Prompt "Press any key to continue"

though I don't expect it to work any better than what you have tried.

Another thing to try - use whatever screen recording software you have available (I use Snagit) to record the PoSh window during execution, then play it back frame by frame to find the error text (if any).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Dave Old Hand Points: 350 More actions · Answer 6

Dave

Old Hand

Points: 350

June 13, 2023 at 3:44 pm

#4210366

cheers Phil , i'll give that a try

Dave Old Hand Points: 350 More actions · Answer 7

Phil

quick update, was decided this was no longer required so it appears i swerved one here!

Thanks for your help on this though, much appreciated