SQL Agent permission issue

kumar-9444 121 Reputation points
2020-11-09T16:17:52.48+00:00

I provided "SQLAgentUserRole" permission to a user to schedule SQL server agent job(version:2008 R2). When the user executes job it is failing with below error

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

If I change the job owner to 'sa' the user can't view the job because SQLAgentUserRole permission was given to a user. How to fix this? I provided this privilege to restrict the use.

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,785 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 111.1K Reputation points MVP
    2020-11-09T23:08:04.72+00:00

    It seems that the service account for SQL Server Agent does not have permission in the AD to get information about this user.

    It is possible to write a stored procedure that permits a user to start a certain job. It requires some understanding of certificates and impersonate to use. I have written about it here:
    http://www.sommarskog.se/grantperm-appendix.html#startjobs
    But as you will find there is a longer article that you need to read first.

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.