Share via

Permission to edit Agent job step - not sysadmin

Loski, Russel 1 Reputation point
2020-08-18T20:37:39.357+00:00

We are trying to reduce the number of sysadmins on our SQL Server. But we have encountered a problem. The user gets an error.

The server is SQL Server 2014 (unknown build, I don't have access). The SSMS that the user uses is also SQL Server 2014.

Setting up problem:

We removed the user from sysadmin role.
We added the user to the SQLAgentOperatorRole role in msdb.
We added the user to ssis_admin role in SSISDB.
We opened the highest folder that we found permissions for in the SSIS catalog node and granted the login permission to every thing except the two manage permissions permissions.
We made the login owner of the job.

When the user logs in, she gets that following error:

Invalid package path "\SSISDB***.dtsx" on the server "". It cannot display the configuration of the package. (Microsoft.DatatransformationServices.DTSExecUI.Controls).

When we add the user back to sysadmin, it works fine.

What can we look at to fix this problem.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,187 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Mark E 76 Reputation points
    2020-08-18T23:26:36.573+00:00

    Check the user permissions on the dtsx file, it’s possible the the user cannot traverse the path to get to it without having an admin credential or without changes to the permissions on the file and preceding file structure

    0 comments
  2. CathyJi-MSFT 21,116 Reputation points Microsoft Vendor
    2020-08-19T07:35:26.657+00:00

    Hi LoskiRussel,

    You can only modify jobs that you own, only sysadmin role members can edit and run jobs owned by others.

    I found an old thread, hope it could give you some ideas.

    Non-sysadmin needs ability to maintain SQL Agent Jobs

    Best regards,
    Cathy

    0 comments