SQL back Job is failing

Question

SQL back Job is failing

ramana3327

SSCoach

Points: 19722
More actions
May 27, 2016 at 1:28 pm

#326614

Hi,
I created a maintenance plan for backup. The job is failing with the error
The job failed. The owner (TestUser) of job MaintenancePlan.Subplan_1 does not have server access.
The TestUser has sysadmin but that is domain account.
After changing the job owner to sa the job ran successfully.
We already applied the latest service pack SP1.
Any idea why the job is failing even the user has sysadmin permissions?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

There is a good bit to a SQL Agent job's execution from a security standpoint, especially a backup one, which needs access to external resources such as a directory to write the backup file. That would be the place I would start looking.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Yes. My account has read/write/execute permissions on the backup directory!

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 3

ramana3327 (5/27/2016)
Hi,
I created a maintenance plan for backup. The job is failing with the error
The job failed. The owner (TestUser) of job MaintenancePlan.Subplan_1 does not have server access.
The TestUser has sysadmin but that is domain account.
After changing the job owner to sa the job ran successfully.
We already applied the latest service pack SP1.
Any idea why the job is failing even the user has sysadmin permissions?

Just because the user has SA permissions, doesn't mean the user has the necessary privs to the disks/folders where the backups are. SA privs have little to do with operating system privs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

VastSQL SSC-Dedicated Points: 36096 More actions · Answer 4

Are you taking backup to a different location or on same server?

Are you getting any error while taking backup manually using the same windows acccount?

SeniorITGuy SSC Eights! Points: 864 More actions · Answer 5

Ramana,

Hopefully you were able to get this working but if not here is something you can look at:

The error you are getting is basically telling you that the account you are using to run backups does not have login rights to SQL. Since this is an Active Directory account, make sure you are able to log in to SQL using the account.

Make sure you still have Windows login enabled on the instance, I had someone change this on one of my instances and it messed up all the jobs until I turned it back on.

Also, if SQL didn't have full access to DNS and AD during startup, like if the power went out and SQL came up before the Domain Controllers then you will have to restart the SQL Agent service before the jobs will work properly.

Hope this helps!

Jon

ramana3327 SSCoach Points: 19722 More actions · Answer 6

I restarted the agent and run the job. But it is giving the same error

We are using only Windows logins. Only apps are using Sql Logins.

I can able to RDP and connect to the SQL Server from there using my Windows logins. Also, I can able to connecct to the SQL Server from my local SSMS.

I verified and my domain(Windows account) has read/write access on the backup directory(Network path).

Manually, I can able to run the backup command but only from the maintenance plan job it is failing.

SeniorITGuy SSC Eights! Points: 864 More actions · Answer 7

Ramana,

Sorry to hear you are still having issues. Is the account assigned sysadmins rights directly or is it part of a group that has sysadmin rights?

If in a group, is that group part of the same domain as the SQL server or a trusted domain?

Jon

Smendle Hall of Fame Points: 3969 More actions · Answer 8

Check the SQL agent account and insure the user running that service has the proper permissions to the network location.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

ramana3327 (5/27/2016)
Hi,
I created a maintenance plan for backup. The job is failing with the error
The job failed. The owner (TestUser) of job MaintenancePlan.Subplan_1 does not have server access.
The TestUser has sysadmin but that is domain account.
After changing the job owner to sa the job ran successfully.
We already applied the latest service pack SP1.
Any idea why the job is failing even the user has sysadmin permissions?

Can you supply more detailed log information?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉