Red Gate forums :: View topic - 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 Previous Versions
SQL Backup Previous Versions forum

Backup Database Permission Denied

Search in SQL Backup Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2  Next
Author Message
stan



Joined: 05 May 2008
Posts: 14

PostPosted: Tue Jun 10, 2008 11:45 pm    Post subject: Backup Database Permission Denied Reply with quote

Hello Support at Redgate

I have a problem backing up a couple of databases in my server, I'm using SQL Backup 5.3.0.178 in SQL Server 2000, this only happens with two databases which hapen to be created from external backup files, when I try to backup them it always gives me the following error:

Error 880: BACKUP DATABASE permission denied in database

I'm using a sysadmin user for SQL Backup authentication, I also tried to add that login as a database user with db_backupoperator and db_owner roles but it still gives me error.

I would appreaciate your help and I appologize if this issue has been previously solved in an older post but i couldn't find information about it.

Kind regards.

Stanly Bolaņos
Back to top
View user's profile Send private message MSN Messenger
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Wed Jun 11, 2008 3:26 am    Post subject: Reply with quote

The backup rights are checked against the account that is currently logged on to SQL Server to run SQL Backup commands.

Are you able to perform a native SQL Server backup on the 2 databases when logged in using the same account?
_________________
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
stan



Joined: 05 May 2008
Posts: 14

PostPosted: Wed Jun 11, 2008 3:59 pm    Post subject: Reply with quote

Yes, using the same user i can make backups of both databases in Enterprise manager or SQL Management Studio, I also made a trace from another database backup made with SQL Backup to verify it effectivily made it with the same user and it does
Back to top
View user's profile Send private message MSN Messenger
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Thu Jun 12, 2008 9:17 am    Post subject: Reply with quote

Since you're already running a trace, could you please try running the script that SQL Backup uses to check for backup rights? The script starts with the comment '-- Testing for BACKUP rights.'.

Can you determine at which point of the check does it decide that the user does not have backup rights?
_________________
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
stan



Joined: 05 May 2008
Posts: 14

PostPosted: Thu Jun 12, 2008 6:41 pm    Post subject: Reply with quote

Where's the script SQL Backup uses for backup rights? do you mean runing the backup script SQL Backup creates for backing up the conflicting database?
Back to top
View user's profile Send private message MSN Messenger
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Fri Jun 13, 2008 1:25 am    Post subject: Reply with quote

If you were to run Profiler while SQL Backup is running,you should see the script that SQL Backup uses to check for backup rights (starts with '-- Testing for BACKUP rights.').

Run this script in Management Studio or Query Analyzer, and see if you can determine where the error lies in the scipt such that it decides that the user does not have backup rights to the specified database.
_________________
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
stan



Joined: 05 May 2008
Posts: 14

PostPosted: Mon Jun 16, 2008 6:40 pm    Post subject: Reply with quote

Hi petey

I tried to run the profiler while sql backup was running, also while it was doing a database backup and the only data text profiler shows me from SQL Backup that says something like test is the following:

-- sp_verify_job_identifiers IF (@sqlagent_starting_test = 'TEST')

or

-- sp_help_jobschedule EXECUTE @retval = sp_verify_job_identifiers '@job_name', '@job_id', @job_name OUTPUT, @job_id OUTPUT, 'NO_TEST'

Otherwise i can't see in the profiler the -- Testing for BACKUP rights.

I am looking all the TSQL events in profiler.

Thanks.
Back to top
View user's profile Send private message MSN Messenger
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Tue Jun 17, 2008 3:55 am    Post subject: Reply with quote

That is strange. Could you please post the contents of the SQL Backup log for the backups that failed? The logs are created in the 'C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>' folder by default.

Thanks.
_________________
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
stan



Joined: 05 May 2008
Posts: 14

PostPosted: Tue Jun 17, 2008 6:20 pm    Post subject: Reply with quote

Here's the log file content:

SQL Backup log file 5.3.0.178

-SQL "BACKUP DATABASE [MonitorMedios] TO DISK = 'E:\BackupSW\<AUTO>.sqb' WITH COMPRESSION = 1 "

----------------------------- ERRORS AND WARNINGS -----------------------------


6/17/2008 11:11:49 AM: Backing up MonitorMedios (full database) to:
6/17/2008 11:11:49 AM: E:\BackupSW\MonitorMedios_20080617_111149.sqb

6/17/2008 11:16:51 AM: Error 880: BACKUP DATABASE permission denied in database: (MonitorMedios)
6/17/2008 11:16:51 AM: Timeout expired
Back to top
View user's profile Send private message MSN Messenger
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Wed Jun 18, 2008 2:36 pm    Post subject: Reply with quote

Im sorry, I should have mentioned that when running Profiler, you need to look at commands ran by the SQL Backup Agent service (SQBCoreService.exe).
_________________
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
stan



Joined: 05 May 2008
Posts: 14

PostPosted: Wed Jun 18, 2008 6:24 pm    Post subject: Reply with quote

Hi petey, thanks for the info, I finally got the testing rights script, but when I try to run it everything seems fine, in the following validation:

DECLARE @hasrights INTEGER
SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin', 'dba')
IF ( @hasrights = 0 )

Which is the very first 'IF' from the script @hasrights value is 1 and finally the script shows me the @hasrights column with 1 and the database name that i can't make backup from.

dba is the user i have configured for SQL Backup and it has sysadmin rights.

Thanks.
Back to top
View user's profile Send private message MSN Messenger
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Thu Jun 19, 2008 8:04 am    Post subject: Reply with quote

Could you please check if there is a setting in the registry for the SQL command timeout?

For 32-bit systems, the location is

HKLM\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\CommandTimeout

For 64-bit systems, the location is

HKLM\Software\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>\CommandTimeout

It appears that this value has been set to 2 seconds, as from the log, the following was recorded:

6/17/2008 11:16:51 AM: Error 880: BACKUP DATABASE permission denied in database: (MonitorMedios)
6/17/2008 11:16:51 AM: Timeout expired

Thanks.
_________________
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
stan



Joined: 05 May 2008
Posts: 14

PostPosted: Thu Jun 19, 2008 10:10 pm    Post subject: Reply with quote

No, there's not a CommandTiemout setting in the registry folder, should I create one?
Back to top
View user's profile Send private message MSN Messenger
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Fri Jun 20, 2008 4:58 am    Post subject: Reply with quote

If it's not there, don't create it just yet.

When you traced the verification code using Profiler, did it stop after 2 seconds? When you ran the code manually, how long did it take to run?

Thanks.
_________________
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
stan



Joined: 05 May 2008
Posts: 14

PostPosted: Mon Jun 23, 2008 6:40 pm    Post subject: Reply with quote

Hi Petey

I was running the testing backup rights script and found something that happens with the databases I can't backup, while running the query from the Management Studio connected to another database (other than the one I can't backup) the script won't finish running (I left the script running more than 30 minutes and nothing) and I noticed that in the middle of the script (inside the first 'IF' statement where it verifies if the user is sysadmin) it has the following statement:

USE [MonitorMedios]

where 'MonitorMedios' is the database I can't backup, but when I remove the use statement the query doesn't delay even 1 second, it runs fast, also if I previously use the Use statement to connect to the database MonitorMedio it runs in 0 seconds.

I don't even know why does this affects the script because it should never get inside the IF becuase my user is a sysadmin, maybe is something with the compiler.

Thanks
Back to top
View user's profile Send private message MSN Messenger
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2  Next
Page 1 of 2

 
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