Red Gate forums :: View topic - Error 880: BACKUP DATABASE permission denied
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Error 880: BACKUP DATABASE permission denied

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Tony Barnsley



Joined: 22 May 2013
Posts: 2

PostPosted: Wed May 22, 2013 1:03 pm    Post subject: Error 880: BACKUP DATABASE permission denied Reply with quote

Sorry to dig this old chestnut up again, but I have just had the need to allow a domain user to back up one database on one of our server, and am getting this error message

Code:
Error 880: BACKUP DATABASE permission denied in database: (AIMS_AdviceLink)

SQL error 4604: There is no such user or group fditbackup'.

SQL Backup exit code: 880
SQL error code: 4604


The user fditbackup has been granted a login and a user created in the relevant database, and is a member of the db_backupoperator role. I tested that the set up was working by running a standard SQL Server Backup command, with the results as below

Code:
BACKUP DATABASE AIMS_AdviceLink TO DISK = 'D:\Backup\AIMS_AdviceLink\FULL_AMIS_AdviceLink.BAK

Processed 12168 pages for database 'AIMS_AdviceLink', file 'AIMS_AdviceLink' on file 1.
Processed 1 pages for database 'AIMS_AdviceLink', file 'AIMS_AdviceLink_log' on file 1.
BACKUP DATABASE successfully processed 12169 pages in 7.671 seconds (12.994 MB/sec).

So as far as SQL Server is concerned the user is set up correctly to backup the database, the issue comes when I try and execute my standard database backup stored procedure, which generates the following SQL Backup Statement and then executes it

Code:
-SQL "BACKUP DATABASE [AIMS_AdviceLink] TO DISK = 'D:\Backup\AIMS_AdviceLink\FULL_AIMS_AdviceLink_201305220935.SQB' WITH INIT , THREADCOUNT = 3 , COMPRESSION = 3 , ERASEFILES = 2"


This stored procedure works fine when executed in Query analyser logged in as the fditbackup user, so far everything is going well, all We have to do now is put it into a command file so that our Tech team can execute it whenever they Upgrade the system rather than bother me. . . The statement is

Code:
ECHO OFF
CLS

SET RunDate=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%
SET RunTime=%TIME:~0,2%%TIME:~3,2%

ECHO AIMS Advice Link Backup Started On %RUNDATE% AT %RunTime%
CD C:\AIMS.Backup

isql -S tcp:SQLAPPS -e -w 2000 -d AIMS_AdviceLink -E -Q "EXECUTE Admin.dbo.sys_Backup @Path = 'D:\Backup\' , @JustDB = 'AIMS_AdviceLink', @Retention = 3, @ForceFull = 1, @MSBackup = 0, @Encrypt = 0, @ThreadCount = 3, @Debug=0"

CD C:\AIMS.Backup


Unfortunately this is where the
Code:
Error 880: BACKUP DATABASE permission denied in database: (AIMS_AdviceLink)

SQL error 4604: There is no such user or group fditbackup'.

SQL Backup exit code: 880
SQL error code: 4604

Occurs. It must be a permissions issue somewhere along the line. I did initially think that it was a problem with isql , but running the Standard SQL Bacuk command above in ISQL worked fine.

I have extracted the Script used to Check Permissions using SQL Profiler, but when I run that in Query Analyser (As the User fditbackup) it returns HasPermissions = 1

One last thought I had was that it could be a database owner problem so I made fditbackup owner of the database but that changed nothing.

I can't run a GUI based backup to test it,as that requires membership of Sysadmin server role which is precisely what I am trying to avoid.

Nothing in the SQL Server logs or event logs to indicate any issue.

Windows Version 2003, SQLServer Version 2000 SP4, SQL Backup Server COmponents 5.3.0.178, SQL Backup GUI 7.3.23

I suppose I could upgrade the Server Components to Version 7.2.3.12, but as we are decommissioning this server soon and moving everything to a SQL 2008 Server with 7.2.3.12 installed I'm trying to avoid that.

Any Further Ideas? I could always switch Permissions Check off, but I feel that that is a workaround of a bug rather than a fix . . .I need to know that it will be fixed when we move the databases to the new server.

Any light that can be shed on this would be a great help
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Thu May 23, 2013 3:27 am    Post subject: Reply with quote

Could you please post the last SQL command, as logged by Profiler, when you run the backup via the sys_Backup procedure?

Best guess would be that the SETUSER function is raising that error. What happens if you run the

Code:
SETUSER 'fditbackup'

when logged on using the same account that the SQL Backup Agent service uses?
_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group