4

This morning I had three failed jobs (no biggy) used for custom reporting

DOMAIN\USER is no longer with the company.

Error Message

The job failed. Unable to determine if the owner (DOMAIN\USER) of job Email Import Impacting Issues has server access (reason: Could not obtain information about Windows NT group/user 'DOMAIN\USER', error code 0x534. [SQLSTATE 42000] (Error 15404)).

I've changed the owner to [sqlAdmin], which is one of our sqlAdmin owner accounts.

6

No, is not a good idea. There is a possible security issue underneath when using sa as owner of the job.

All jobs are ran through SQL Server Agent and it sets the security context for job execution based on the role of the user owning the job. So, if you use sa then you know what it will happen. By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account. The exception to this rule is T-SQL job steps, which execute under the security context of the job owner. If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.

So, using sa as the job owner will cause all T-SQL job steps to execute as the SQL Agent service account, which is a system administrator account. A better option is to set a non-sysadmin account as the job owner, and explicitly grant only the required database permissions to this account.

So, what I would do is to create a specific user on the DOMAIN and give that user the needed rights to execute those jobs. And that user should not be a person in the company as you could have same issue in the future, if that person goes away and the account is closed...same error again.

  • 1
    Rather than creating a new AD user why not use the AD Group that contains the users that are in charge of that job/process/application? – Kenneth Fisher Jun 26 '15 at 16:18
  • 1
    @kennethFisher, good point, we actually use this type of approach, creating AD gropus and including there the users, that way management is done way easier – Yaroslav Jun 26 '15 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.