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 Previous  1, 2
Author Message
petey



Joined: 24 Apr 2005
Posts: 2305

PostPosted: Tue Jun 24, 2008 7:41 am    Post subject: Reply with quote

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.
_________________
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 24, 2008 9:46 pm    Post subject: Reply with quote

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
WHILE @@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
WHILE @@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
Back to top
View user's profile Send private message MSN Messenger
stan



Joined: 05 May 2008
Posts: 14

PostPosted: Wed Jul 02, 2008 6:38 pm    Post subject: Reply with quote

Dear Petey

Sorry, have you found any solution for my problem?

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



Joined: 16 Oct 2008
Posts: 3

PostPosted: Fri Oct 17, 2008 12:03 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2305

PostPosted: Fri Oct 17, 2008 7:40 am    Post subject: Reply with quote

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

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


does it also raise the same error?
_________________
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
ssb



Joined: 16 Oct 2008
Posts: 3

PostPosted: Mon Oct 20, 2008 4:08 am    Post subject: Redbackups Reply with quote

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.
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2305

PostPosted: Mon Oct 20, 2008 8:07 am    Post subject: Reply with quote

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.
_________________
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
ssb



Joined: 16 Oct 2008
Posts: 3

PostPosted: Thu Oct 23, 2008 6:46 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
kedwards



Joined: 16 Nov 2007
Posts: 7

PostPosted: Mon Mar 28, 2011 7:54 pm    Post subject: Error 880: BACKUP DATABASE permission denied in database Reply with quote

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
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2305

PostPosted: Tue Mar 29, 2011 4:26 am    Post subject: Reply with quote

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.
_________________
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
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page Previous  1, 2
Page 2 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