What are the challenges you face when working across database platforms? Take the survey
Options

Error 880: BACKUP DATABASE permission denied

Tony BarnsleyTony Barnsley Posts: 3
edited May 22, 2013 10:27PM in SQL Backup Previous Versions
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
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
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
-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
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
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

Comments

  • Options
    peteypetey Posts: 2,358 New member
    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
    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 8
Sign In or Register to comment.