Error 880: BACKUP DATABASE permission denied

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Error 880: BACKUP DATABASE permission denied

Postby Tony Barnsley » Wed May 22, 2013 1:03 pm

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: Select all
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: Select all
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: Select all
-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: Select all
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: Select all
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
Tony Barnsley
 
Posts: 2
Joined: Wed May 22, 2013 11:22 am

Postby petey » Thu May 23, 2013 3:27 am

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: Select all
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
petey
 
Posts: 2233
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests