Least Privilege for SQLPS via SQLAgent Jobs

Tagged: 

This topic contains 1 reply, has 2 voices, and was last updated by Profile photo of Richard Siddaway Richard Siddaway 3 years, 2 months ago.

  • Author
    Posts
  • #13258
    Profile photo of Ken Row
    Ken Row
    Participant

    Here's a two-liner that aborts when I run it as a SQL Agent job in SSMS:

    $hostname=$env:computername
    set-location "sqlserver:\sql\$hostname"

    Here's the info from the job history:

    A job step received an error at line 2 in a PowerShell script.
    The corresponding line is 'set-location "sqlserver:\sql\$hostname"'.
    Correct the script and reschedule the job.
    The error information returned by PowerShell is: 'Cannot find path "SQLSERVER:\sql\C27TWSQLCRAN01v" because it does not exist.'

    I've found that if the account used to run the job has admin-level privileges, as is well, but when I remove the account from the admin group, I get the abort.

    Might anyone know off-hand the minimum privileges needed to execute sqlps jobs that use the SQLSERVER provider?

  • #13305
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

    I've found that SQL Agents jobs need to be run with a high level of privilege for PowerShell steps to work. I think you'll probably need to stick with admin level rights

You must be logged in to reply to this topic.