Error 880: BACKUP DATABASE permission denied
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: 4604Occurs. 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 18.104.22.168, SQL Backup GUI 7.3.23
I suppose I could upgrade the Server Components to Version 22.214.171.124, but as we are decommissioning this server soon and moving everything to a SQL 2008 Server with 126.96.36.199 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