2

SQL Server 2008R2 is failing to execute log backup hourly.

Date 5/2/2013 10:22:19 PM
Log Job History (LOG BACKUP)

Step ID 0
Server pc
Job Name LOG BACKUP
Step Name (Job outcome)
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The owner (pc\user) of job LOG BACKUP does not have server access.

ps: its a windows 7 x86 ultimate virtual machine.
the account have administrator privilege. any idea why its failing?

Improve this question
7
  • 1
    Where does the account have 'adminstrator' privileges? Note, server privileges and SQL privileges are different and just because the account is a SQL admin doesn't mean it is a server admin. Also, are you backing up to a local share or a network share?
    – Mike Fal
    Commented May 2, 2013 at 19:35
  • It's a local path, plus the windows user have sysadmin privilege.
    – Tawfik Khalifeh
    Commented May 2, 2013 at 19:40
  • 1
    By sysadmin, do you mean the SQL Server sysadmin? This isn't the same as server/filesystem permissions. What are the login's permissions on the backup location?
    – Mike Fal
    Commented May 2, 2013 at 19:48
  • 2
    Have you tried executing a log backup using T-SQL logged in as this user to the same location? If this fails, what error message do you get?
    – Mike Fal
    Commented May 2, 2013 at 19:58
  • 1
    Are local administrators specifically designated as sysadmin? Or at least is this one? 2008 R2 discontinued the practice of automatically adding BUILTIN\Local Administrators to sysadmin.
    – swasheck
    Commented May 3, 2013 at 0:30

2 Answers 2

Reset to default
0

Try to create the PC\user as a login and give him the backup privilege in your database with this code.

USE [master]
GO
CREATE LOGIN [SERVER\Test1] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [mydb]
GO
CREATE USER [SERVER\Test1] FOR LOGIN [SERVER\Test1]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [SERVER\Test1]
GO
Improve this answer
2
  • sir, i know that very will, the user account is a sysadmin member, i just can't figure out why in gods sake things are going this way. I've already created another account, added a login, mapped a user to that login, added the user to db_backuporperator, and change the job's owner (exactly as you stated).
    – Tawfik Khalifeh
    Commented May 10, 2013 at 17:13
  • Can you provide the Windows Event log associated? My guess is the problem is not in SQL Server but it has to do with Windows. Which version are you using? Is your PC member of a domain?
    – PollusB
    Commented May 12, 2013 at 5:07
1

I recently had the same issue in SQL Server 2019. My solution was changing the job Owner from the [domain\user] to SQL Server Agent account, and it worked.

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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