SQL Agent permission issue

Question

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.

Answer 1

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.