Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 May 2 '13 at 19:35
    
It's a local path, plus the windows user have sysadmin privilege. –  sarepta May 2 '13 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 May 2 '13 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 May 2 '13 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 May 3 '13 at 0:30
show 2 more comments

1 Answer 1

up vote 0 down vote accepted

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
share|improve this answer
    
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). –  sarepta May 10 '13 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 May 12 '13 at 5:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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