Backup Database Permission Denied

stan · June 10, 2008 6:45PM

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

petey · June 10, 2008 10:26PM

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?

stan · June 11, 2008 10:59AM

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

petey · June 12, 2008 4:17AM

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?

stan · June 12, 2008 1:41PM

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?

petey · June 12, 2008 8:25PM

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.

stan · June 16, 2008 1:40PM

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.

petey · June 16, 2008 10:55PM

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.

stan · June 17, 2008 1:20PM

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

petey · June 18, 2008 9:36AM

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).

stan · June 18, 2008 1:24PM

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.

petey · June 19, 2008 3:04AM

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.

stan · June 19, 2008 5:10PM

No, there's not a CommandTiemout setting in the registry folder, should I create one?

petey · June 19, 2008 11:58PM

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.

stan · June 23, 2008 1:40PM

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

petey · June 24, 2008 2:41AM

So you mean that if you were connected to the MonitorMedios database and you ran the script, it works fine, but if you were connected to another database and ran the script, it hangs?

Thanks.

stan · June 24, 2008 4:46PM

Yes, I don't why this happens, here is the code I am trying:

-- Testing for BACKUP rights.
-- Work from server role to database role, to BACKUP DATABASE execution and restriction rights.
-- # Aliases are not supported.

DECLARE @hasrights INTEGER
SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin', 'dba')
IF ( @hasrights = 0 )
OR ( @hasrights IS NULL )
BEGIN
SETUSER 'dba'
-- As long as the user has sysadmin server role, he can backup the database.
SELECT @hasrights = IS_SRVROLEMEMBER('sysadmin')
IF ( @hasrights = 0 )
OR ( @hasrights IS NULL )
BEGIN
-- This has to be done because IS_SRVROLEMEMBER does not check recursively if a login name is provided.

USE [MonitorMedios]

DECLARE @login_type INTEGER
DECLARE @login_name SYSNAME
-- No sysadmin rights. Check for database db_owner role.
SELECT @hasrights = IS_MEMBER('db_owner')
-- Check for database db_backupoperator role.
IF ( @hasrights <> 1 )
BEGIN
SELECT @hasrights = IS_MEMBER('db_backupoperator')
END
-- No db_backupoperator role. Check BACKUP DATABASE execute rights.
-- Quick check for explicit rights.

IF ( @hasrights = 0 )
OR ( @hasrights IS NULL )
BEGIN
SELECT @hasrights = 1
WHERE EXISTS ( SELECT 1
FROM sysprotects a
INNER JOIN sysusers b ON a.uid = b.uid
AND b.name = 'dba'
WHERE a.action = 228
AND a.protecttype IN ( 204, 205 ) )
IF ( @hasrights = 0 )
OR ( @hasrights IS NULL )
BEGIN
-- No direct rights. Now need to iterate and check all Windows groups and SQL Server roles.
DECLARE cur_rights CURSOR
FOR SELECT ( CASE WHEN b.isntgroup = 1
THEN 1
ELSE CASE WHEN b.issqlrole = 1 THEN 2
ELSE 0
END
END ) type,
b.name
FROM sysprotects a
INNER JOIN sysusers b ON a.uid = b.uid
WHERE a.action = 228
AND a.protecttype IN ( 204, 205 )
AND ( b.isntgroup = 1
OR b.issqlrole = 1
)
OPEN cur_rights
FETCH NEXT FROM cur_rights INTO @login_type,
@login_name
@FETCH_STATUS = 0
BEGIN
SELECT @hasrights = IS_MEMBER(@login_name)
IF @hasrights = 1
BEGIN
BREAK
END
FETCH NEXT FROM cur_rights INTO @login_type,
@login_name
END
CLOSE cur_rights
DEALLOCATE cur_rights
END
END

-- for database level rights, need to check if DENY restriction exists
IF @hasrights = 1
BEGIN
SELECT @hasrights = 0
WHERE EXISTS ( SELECT 1
FROM sysprotects a
INNER JOIN sysusers b ON a.uid = b.uid
AND b.name = 'dba'
WHERE a.action = 228
AND a.protecttype IN ( 206 ) )
IF @hasrights = 1
BEGIN
-- No explicit DENY restrictions. Check via group / role membership.
DECLARE cur_rights CURSOR
FOR SELECT ( CASE WHEN b.isntgroup = 1
THEN 1
ELSE CASE WHEN b.issqlrole = 1 THEN 2
ELSE 0
END
END ) type,
b.name
FROM sysprotects a
INNER JOIN sysusers b ON a.uid = b.uid
WHERE a.action = 228
AND a.protecttype = 206
AND ( b.isntgroup = 1
OR b.issqlrole = 1
)
OPEN cur_rights
FETCH NEXT FROM cur_rights INTO @login_type,
@login_name
@FETCH_STATUS = 0
BEGIN
SELECT @hasrights = IS_MEMBER(@login_name)
IF @hasrights = 1
BEGIN
SET @hasrights = 0
BREAK
END
FETCH NEXT FROM cur_rights INTO @login_type,
@login_name
END
CLOSE cur_rights
DEALLOCATE cur_rights
END
END

END
SETUSER
END

DECLARE @dbname SYSNAME
SELECT @dbname = name
FROM master..sysdatabases
WHERE name = N'MonitorMedios'
IF @hasrights = 1
SELECT CAST(1 AS INT) AS hasrights,
@dbname AS name
ELSE
SELECT CAST(0 AS INT) AS hasrights,
@dbname AS NAME

stan · July 2, 2008 1:38PM

Dear Petey

Sorry, have you found any solution for my problem?

Thanks.

ssb · October 16, 2008 7:03PM

Hi,
Has anyone found the solution for this?
We're having a same problem. This is the code our backup job uses:
ECLARE @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES [*] TO DISK = ''d:\TL_dir\<AUTO>.sqb'' WITH COMPRESSION = 3, ERASEFILES = 1, THREADCOUNT = 4"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

There are 70 databases on this server. The job backups all, but one and the following message is displayed:
Error 880: BACKUP DATABASE permission denied in database: (distmodel)
Timeout expired

Thank you.

petey · October 17, 2008 2:40AM

If you backed up the just that database itself e.g.

EXEC master..sqlbackup '-sql "BACKUP DATABASE distmodel TO DISK = ''d:\TL_dir\&lt;AUTO&gt;.sqb'' WITH COMPRESSION = 3, ERASEFILES = 1, THREADCOUNT = 4"'

does it also raise the same error?

ssb · October 19, 2008 11:08PM

Peter,

It fails with the same error:

d:\TL_dir\FULL_smvbmec007a_distmodel_20081020_140009.sqb

Error 880: BACKUP DATABASE permission denied in database: (distmodel)
Timeout expired

Regards,
Sardara.

petey · October 20, 2008 3:07AM

Could you please use SQL Profiler, capture the script that SQL Backup uses to validate the backup rights, run the script manually using Query Analyzer/Management Studio, and let me know which part of the script causes the timeout?

Thanks.

ssb · October 23, 2008 1:46AM

Thanks Peter.
I think, the problem is with database ownership. I'm trying get client's permissions to re-establish the ownership of the database.
Regards,
Sardara.

kedwards · March 28, 2011 2:54PM

I am using SQL Backup v6.4.0.56 We are trying to setup SQL service accounts with the least amount of security privleges needed to run. We have given the Red Gate service account sa rights to SQL, but when we try to backup DBs we get the error below. Does SQL agent account also have to be an SA for Red Gate to work properly? If so this totally defeats our least privileges model we are trying to go to.

Error 880: BACKUP DATABASE permission denied in database: (Test)
SQL error 15157: Setuser failed because of one of the following reasons: the database principal 'SQL_Agent' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated,
or you do not have permission.

Thanks,
Keith

petey · March 28, 2011 11:26PM

When SQL Backup backs up a database, it checks that the user running the backup has adequate rights to back up that database. In your case, you will need to assign the SQL Server Agent service startup account rights to back up the databases listed in your job(s). You can do this using the GRANT BACKUP DATABASE rights, or assign the db_backupoperator database role, to the account. You do not need to assign sa rights.

Another option is to have SQL Backup skip the check for rights. You can do this by creating a registry entry (DWORD type) named 'SkipChecks', and assign it the value '1'. The registry entry is created in HKEY_LOCAL_MACHINE\Software\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>. However, this setting will apply to all SQL Backup processes, and anyone who has rights to run the 'sqlbackup' extended stored procedure will be able to back up and restore any databases.

Backup Database Permission Denied

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk