locked
Create and run jobs in SQL RRS feed

  • Question

  • Hello

    I am trying to grant a user permission to create and run her own job in SQL. I have granted the user 'SQL Agent Operator Role' which lets her create the job. But when she executes the job, it fails with the below error message:

    Message
    The job failed.  Unable to determine if the owner (domain\user) of job <JOBNAME> has server access (reason: Could not obtain information about Windows NT group/user 'domain\user', error code 0x5. [SQLSTATE 42000] (Error 15404)).

    She is able to execute the job is the job owner is changed to 'sa'. But the user does not have the permissions to switch the job owner to 'sa', because changing job ownership requires sa privileges. How can I have the user execute the job without getting the above error message?

    Thanks

    Thursday, December 8, 2016 11:14 PM

All replies

  • http://www.sqldbadiaries.com/2011/02/25/unable-to-determine-if-the-owner-of-job-has-server-access/

    http://www.sql-server-performance.com/forum/threads/sql-login-permissions-to-run-agent-job.32467/ -->try use proxy account in sql server


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    • Proposed as answer by philfactor Monday, December 12, 2016 10:25 PM
    Friday, December 9, 2016 3:10 AM
  • Hi Krishna,

    Did you try to check with a login who is having sysadmin privileges?

    Thanks


    Ramesh. M

    Friday, December 9, 2016 4:57 AM

  • https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f703b249-3a39-4503-88fb-0fc619a58c82/error-code-0x5-sqlstate-42000-error-15404-could-not-obtain-information-about-windows-nt?forum=sqltools

    https://social.msdn.microsoft.com/Forums/en-US/98b89917-ca6e-4e60-9b9a-e9422c5dfb3c/what-permissions-should-user-have-to-create-a-job-in-sql-server-agent-and-to-run-the-package-on?forum=sqlintegrationservices

    Saturday, December 10, 2016 11:02 AM
  • If the job is only going to be executed interactively (on demand), by the user then you will need to have it execute as a proxy account. If, however, the job will be scheduled and allowed to run on a predefined schedule, then it will execute under the security context of the SQL Agent Service startup account. Typically, that service startup account will have local administrator privilege on the server and/or Sysadmin fixed server role in the SQL instance (or else it can be put in that role by you). That way job execution will succeed when executed on the schedule.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Saturday, December 10, 2016 10:52 PM
  • Thank you for the suggestion, but the 'Run as' option to use a proxy account is available only if the job step is 'Cmdexec'. It cannot be assigned a proxy if the job step is a T-SQL step.

    What are the options if if a non-sysadmin user needs to execute the job with T-SQL step?

    Tuesday, December 20, 2016 10:49 PM
  • The error message you get makes me think that the service account for SQL Server Agent lacks permission in Active Directory.

    Wednesday, December 21, 2016 10:10 PM