Thursday, January 6, 2011

Sql Job Failure - Unable to Determine if the Owner (PROD\tuitionaffordable) of Job has Server Access "error code 0x2"



Unable to determine if the owner (PROD\tuitionaffordable) of job  has server access error code 0x2

Got following error for some job which runs every day without any issue-
Error 
Date                      1/6/2011 2:25:00 PM
Log                         Job History (Tuitionaffordable-REPLDB-Tuition-28)

Step ID                 0
Server                   Tuitionaffordable
Job Name                            Tuitionaffordable-REPLDB-Tuition-28
Step Name                         (Job outcome)
Duration                              00:00:09
Sql Severity                        0
Sql Message ID                 0
Operator Emailed                           
Operator Net sent                          
Operator Paged                               
Retries Attempted                          0

Message
The job failed.  Unable to determine if the owner (PROD\tuitionaffordable) of job Tuitionaffordable-REPLDB-Tuition-28 has server access (reason: Could not obtain information about Windows NT group/user 'PROD\tuitionaffordable', error code 0x2. [SQLSTATE 42000] (Error 15404)).

Let's Understand: The error is thrown because the account PROD\tuitionaffordable is either disabled or doesn't have access to the shared folders where the job tries to save/access files.

Solution: Change the owner of the job to some other account which has access to the shared folders where the job tries to save/access files.



Another Problem: What if there are 50 jobs with the same owner id and I want all of them to change.

Solution:
UPDATE sysjobs
SET    owner_sid = 0x01
FROM   sysjobs
INNER  JOIN  sysjobhistory hist
ON     hist.job_id = sysjobs.job_id
AND    hist.run_status = 0
AND    hist.message LIKE '%PROD\tuitionaffordable%'

Note: When the owner of the job is sa then the job runs under the account under which sql agent runs.



Custom Search






No comments:

Post a Comment