locked
SQL Agent Job--"a required privilege is not held by the client" RRS feed

  • Question

  • Hello All, 

    I have spent more than 4 hours to try completing this task as it was thought probably only stealing me 2 hours at most. 

    Here is story. I am trying to give some junior users with minimum permission of executing the the jobs they own.

    So, first, SQL login "JobOperator" was created. And granted the role of "SQLAgentUserRole" in msdb to this login. Then change one of jobs' owner from sa to "JobOperator". That is the only one they need to execute.

    With this setup, my test was NOT passed. The error says Non-sysadmin cannot execute DTS packge, which makes sense as this job contains 2 steps, and one is to execute file location SSIS package and the other to process the SSAS cube with SSAS command.

    Then, I created the credential with local admin/sysadmin identity. Further, created the proxy account bounding with the credential and link to all subsystems including SSAS command, SSAS query and SSIS package. Also, add the principal "JobOperator" into this proxy account.

    Finally, change my steps in this job to be run as "Proxy account". Test it again, still failed. It says "The step XXXXX cannot be created(a required privilege is not held by the client)"

    Does anyone share some thoughts on this?

     


    Derek


    • Edited by Derek Dai Thursday, July 30, 2015 7:54 PM
    Thursday, July 30, 2015 7:42 PM

All replies

  • Did u tried this ..

    1. Add SQL Server Service account at SQLServer2~MSSQLUser$ComputerName$MSSQLSERVER windows Group
    2. Add SQL Server Service account at  SQLServer2~SQLAgentUser$ComputerName$MSSQLSERVER windows Group
    3. Restart your server

    https://msdn.microsoft.com/en-us/library/ms365151.aspx


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Thursday, July 30, 2015 7:50 PM
  • Just tried. 

    The same error.


    Derek

    Thursday, July 30, 2015 8:08 PM
  • That second step should be to add *agent* to that group, not the database engine service account, right?

    Personally, I always prefer to assign service account using the right tool, which should take care of all permission assignments. That tool, is of course, SQL Server Configuration Manager.


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, July 30, 2015 9:18 PM
  • TiborK, 

    For both steps, I am using "Proxy Account". And Proxy account is under the credential(domain service account), which is the admin for DB engine/SSIS/SSAS.

    All services and launching accounts were setup through SQL Server Configuration Manager

    Not sure what exactly you mean.

     

    Derek

    Thursday, July 30, 2015 10:05 PM
  • If you configured the service account using that tool, then we can set that part aside.

    Is the service account for Agent a domain account or a local account? If it is a local account, can you, as a test, try a domain account?

    I get the part about the credential and proxy. The error message seems to suggest that the OS doesn't allow agent to start a process using as he user specified in the credential. Hence my suggestion about using the right tool to change service account.


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, July 30, 2015 10:16 PM
  • The service account is , as a best practice, a domain account. This account is used to launch SSIS, DB engine, SSAS and this account is in local admin group.

    Also, this account has been set as sysadmin in DB engine and system admin in SSAS instance.


    Derek

    Friday, July 31, 2015 12:17 AM
  • Here is story. I am trying to give some junior users with minimum permission of executing the the jobs they own.

    If a login owns a job he can definitely execute it what additional permission you want to give and why ?


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Friday, July 31, 2015 4:22 AM
  • It could be my misunderstanding. I think even he is the owner, but this login still need to be in the role of "SQLAgentUserRole" in MSDB 

    Derek

    Friday, July 31, 2015 4:30 AM