Red Gate forums :: View topic - Error 880: BACKUP DATABASE permission denied in database
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Error 880: BACKUP DATABASE permission denied in database

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
devdept



Joined: 19 Jan 2009
Posts: 22

PostPosted: Thu Oct 17, 2013 9:31 am    Post subject: Error 880: BACKUP DATABASE permission denied in database Reply with quote

Hi All,

I am evaluating SQL Backup Pro and getting this error during backup:

Quote:
Error 880: BACKUP DATABASE permission denied in database: (Tfs_configuration)


Two questions for you:

1) Security: we are changing admin server password periodically, will this be a problem for SQL backup Pro?

2) What user type/privileges is the best for SQL backup tool?

Thanks,

Alberto
Back to top
View user's profile Send private message
Chris.Allen



Joined: 12 Mar 2009
Posts: 585

PostPosted: Fri Oct 18, 2013 10:45 am    Post subject: Reply with quote

Hi Alberto,

this should be the best resource for you:

http://www.red-gate.com/supportcenter/content/knowledgebase/SQL_Backup/KB200710000173



In general- changing your 'admin' password will not affect the SQL backup agent.

I always default to running the SQL Backup agent as Local System.
Back to top
View user's profile Send private message
devdept



Joined: 19 Jan 2009
Posts: 22

PostPosted: Fri Oct 18, 2013 10:50 am    Post subject: Reply with quote

Thanks Chris,

I'll check it and let you know.

Alberto
Back to top
View user's profile Send private message
devdept



Joined: 19 Jan 2009
Posts: 22

PostPosted: Fri Oct 18, 2013 11:21 am    Post subject: Reply with quote

Hi Chris,

I gave a look to the article you linked. By the way I am doing the backup of several databases at once and only one is failing with the error above.

Wouldn't this mean that so far privileges are good enough?

Thanks,

Alberto
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Tue Oct 22, 2013 11:36 am    Post subject: Reply with quote

Thanks for your reply.

That would indicate that the user that initiates the job doesn't have permission to backup that particular backup.

If the job is started by the SQLAgent, check that the logon account for the SQLAgent service has permissions to backup the database in question.

Can you backup the database manually using the SQL Backup UI?
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
devdept



Joined: 19 Jan 2009
Posts: 22

PostPosted: Tue Oct 22, 2013 11:46 am    Post subject: Reply with quote

No, even from UI I get the same error.

What I really would like to see, it would be a small movie that teaches how to build the perfect domain user + SQL server login for SQL Backup Pro. In the documentation provided you assume that end-user are all sys admins and this is not always like that.

In addition, SQL Backup Pro, after a periodic password change was not doing scheduled backup anymore because the SQL Server Agent was stopped. I think you should notify this.

Thanks,

Alberto


Last edited by devdept on Tue Oct 22, 2013 12:28 pm; edited 1 time in total
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Tue Oct 22, 2013 12:04 pm    Post subject: Reply with quote

This is actually something that people have been requesting for a while, but unfortunately we don't have any plans to change the way it works at the moment.

There is a workaround you can use to allow non sysadmin users to backup. See the following topic:
http://www.red-gate.com/MessageBoard/viewtopic.php?p=27970#27970

We don't have any useful training videos for setting up service rights (which we probably should). However, where 3rd party SQL Server backup tools are concerned, they mostly all leverage a SQL Server virtual device, and in order to create a virtual device, the account needs to be a member of the sysadmin fixed server role. We can't really get around it, so you don't really get a lot of choices with regards to the types of account you can use for the services.

The logon account for the UI or the SQLAgent service will be 'users' for the job, so they should be able to be non sysadmins. I apologize that isn't possible with SQL Backup, but the workaround should hopefully help.
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
devdept



Joined: 19 Jan 2009
Posts: 22

PostPosted: Tue Oct 22, 2013 1:45 pm    Post subject: Reply with quote

After changing the user as for the link provided (http://www.red-gate.com/supportcenter/content/knowledgebase/SQL_Backup/KB200710000173 ) now I get:

SQL Backup could not access [HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal(LOCAL)] running as user [backup]

What do I need to do?

Thanks,

Alberto
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Tue Oct 22, 2013 2:00 pm    Post subject: Reply with quote

I believe that means you need to make sure the logon account for the SQL Backup agent has permissions to that key.

If you open up regedit on the server, you should be able to find the key. I suggest granting full access for the SQL Backup agent logon account to BackupSettingsGlobal.

I hope this helps.
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
devdept



Joined: 19 Jan 2009
Posts: 22

PostPosted: Tue Oct 22, 2013 2:08 pm    Post subject: Reply with quote

Done Chris, thanks for your help. I am backing-up now. Let's see what happens.

The "Ingenuos Simple Tools" slogan should not involve these permission issues. Smile
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Tue Oct 22, 2013 2:35 pm    Post subject: Reply with quote

That's great. Maybe we should rebrand to 'ingenuinely simple' Smile
_________________
Chris
Back to top
View user's profile Send private message Send e-mail
devdept



Joined: 19 Jan 2009
Posts: 22

PostPosted: Tue Oct 22, 2013 2:49 pm    Post subject: Reply with quote

Please consider this issue solved. We will proceed with the purchase.
Back to top
View user's profile Send private message
devdept



Joined: 19 Jan 2009
Posts: 22

PostPosted: Wed Oct 23, 2013 7:53 am    Post subject: Reply with quote

Hi Chris,

Unfortunately, even following all your suggestions (user (x), login(y), logon as service privilege, registry edit, etc.)

This morning I still get this notification. Nothing has changed.

What else should I check?

Thanks,

Alberto


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


10/23/2013: Backing up Tfs_Configuration (differential database) to:
10/23/2013: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\DIFF_(local)_Tfs_Configuration_20131023_000000.sqb

10/23/2013 12:00:01 AM: Error 880: BACKUP DATABASE permission denied in database: (Tfs_Configuration)
Back to top
View user's profile Send private message
devdept



Joined: 19 Jan 2009
Posts: 22

PostPosted: Wed Oct 23, 2013 9:44 am    Post subject: Reply with quote

I cannot access the Zendesk ticket Chris, if I use the RG account I have no tickets inside it. If I register a new one it's the same.

What account should I use? Do you mind associating the ticket to our RG account e-mail?

Thanks,

Alberto
Back to top
View user's profile Send private message
Chris Auckland



Joined: 24 Oct 2006
Posts: 757
Location: Red Gate Software Ltd.

PostPosted: Wed Oct 23, 2013 9:49 am    Post subject: Reply with quote

We have your Zendesk account associated with the same email address you have used for this forum. If you create your RedGateID with that same email address, you should see the tickets. Alternatively, you can just reply to the ticket instead of logging into the support portal.

Sorry for the inconvenience.
_________________
Chris
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
Page 1 of 1

 
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