Backup Error "Permission Denied in Database"


#1

I am receiving the following error when backing up. This only just started yesterday. Something must have changed on our server as our IT people were working on it yesterday. Here is the log. Can you send me in the right direction to get this fixed? Thanks!

4/12/2018 9:53 AM Starting job “Backup Job - 1” - “Full”. Computer “SERVER”. App v.“11.6.1”
4/12/2018 9:53 AM The backup folder “C:\Program Files\Microsoft SQL Server\MSSQL13.TM_SQLEXPRESS\MSSQL\Backup” has “877.175GB” free space. The temporary folder “C:\Users\Administrator.SAC\AppData\Local\Temp\Pranas.NET\SQLBackupAndFTP\backup” has “877.175GB” free space
4/12/2018 9:53 AM Backing up “.\TM_SQLEXPRESS” LocalSqlServer databases
4/12/2018 9:53 AM Getting maximum file size…
4/12/2018 9:53 AM The maximum file size: “”
4/12/2018 9:53 AM Backing up “master” database to .bak file. Backup type: “Full”. Checksum: “Disabled”
4/12/2018 9:53 AM Failed to backup “master” database with “Full” backup type: BACKUP DATABASE permission denied in database ‘master’.
BACKUP DATABASE is terminating abnormally.
4/12/2018 9:53 AM Backing up “model” database to .bak file. Backup type: “Full”. Checksum: “Disabled”
4/12/2018 9:53 AM Failed to backup “model” database with “Full” backup type: The server principal “SAC\Administrator” is not able to access the database “model” under the current security context.
BACKUP DATABASE is terminating abnormally.
4/12/2018 9:53 AM Backing up “msdb” database to .bak file. Backup type: “Full”. Checksum: “Disabled”
4/12/2018 9:53 AM Failed to backup “msdb” database with “Full” backup type: BACKUP DATABASE permission denied in database ‘msdb’.
BACKUP DATABASE is terminating abnormally.
4/12/2018 9:53 AM Backing up “TimeMatters” database to .bak file. Backup type: “Full”. Checksum: “Disabled”
4/12/2018 9:53 AM Failed to backup “TimeMatters” database with “Full” backup type: The server principal “SAC\Administrator” is not able to access the database “TimeMatters” under the current security context.
BACKUP DATABASE is terminating abnormally.
4/12/2018 9:53 AM Backing up “TimeMatters_Tutor” database to .bak file. Backup type: “Full”. Checksum: “Disabled”
4/12/2018 9:53 AM Failed to backup “TimeMatters_Tutor” database with “Full” backup type: The server principal “SAC\Administrator” is not able to access the database “TimeMatters_Tutor” under the current security context.
BACKUP DATABASE is terminating abnormally.
4/12/2018 9:53 AM Cleaning up backups of “Backup Job - 1” job
4/12/2018 9:53 AM Job “Backup Job - 1” finished with “5” errors
4/12/2018 9:53 AM Sending job log to gmartin@sachicago.com
4/12/2018 9:53 AM Sending report to ${https://sqlbackupandftp.com/weblog?jobid=d1f0fb48-0949-4456-b922-9143102a53d3|Web Log}


#2

Hello,

Thank you for the details, we have checked your log, you get the following error message:

Failed to backup “TimeMatters” database with “Full” backup type: The server principal “SAC\Administrator” is not able to access the database “TimeMatters” under the current security context.
BACKUP DATABASE is terminating abnormally.

It means that user haven’t enough permission to backup the database. Please check to see if the user is mapped to the DB you are trying to log into. You can do it via SQL Server Management Studio.


#3

Alexander,

I am running into a similar issue. I know that the user has read/write access to the database I am attempting to connect to and backup, what specific permission needs to be set to allow backups?

I appreciate any help, thank you!
Rob

Edit: I reviewed permissions more closely in the subject database and found the explicit permission named: “Backup database” with the options for Grant, With Grant, Deny available. You can ignore my question :smile:


#4

Hi Rob,

Thanks for the feedback. Please feel free to contact us on any issues or concerns.


#5

The permission issue seems to have been sorted, now it looks like a storage issue is preventing the job from completing. It’s in my web log too, but the details are these:
1:17:27 PM : Starting job "Backup Job - 1 " - “Full”. Computer “PROD-SPICEWORKS”. App v.“11.6.3”
1:17:27 PM : The backup folder “C:\sql_backups\Pranas.NET\SQLBackupAndFTP\backup” has “420.460GB” free space. The temporary folder “C:\sql_backups\Pranas.NET\SQLBackupAndFTP\backup” has “420.460GB” free space
1:17:27 PM : Backing up “sausage.capitol.office” LocalSqlServer databases
1:17:27 PM : Getting maximum file size…
1:17:27 PM : The maximum file size: “”
1:17:27 PM : Backing up “master” database to .bak file. Backup type: “Full”. Checksum: “Disabled”
1:17:28 PM : ERROR: Failed to backup “master” database with “Full” backup type: Cannot open backup device ‘C:\sql_backups\Pranas.NET\SQLBackupAndFTP\backup\master201805181317.bak’. Operating system error 3(The system cannot find the path specified.). BACKUP DATABASE is terminating abnormally.
1:17:28 PM : Cleaning up backups of "Backup Job - 1 " job
1:17:28 PM : ERROR: Job "Backup Job - 1 " finished with “1” errors

This is just a folder I created on the device running the application to test having the job complete. Do I need to alter security permissions on the destination folder to include something other than the user logged into the computer running SQL Backup and FTP?

Thank you,
Rob


#6

Hi Rob,

Thanks for the details.

Please make sure that the user from behalf whom SQL Server runs has enough permissions to the selected folder. Also, we recommend you to grant permissions to the following user - “local system”.


#7

Getting the same error, but only during scheduled backups. Running a backup manually works perfectly using Windows Authentication. Testing the connection in the “Connect to Server” window returns a successful result.

I’ve enabled all access for the Admin user in SQL Server Mgnt Studio.

Lastly, when I switch credentials to a database user/pass then a scheduled backup will run, but it will eventually fail since the sql creds do not have permission to access the destination directory.

Any advice to help solve this issue? Thx.


#8

Hi Posey_Hedges,

Thanks for the details please give us some time to investigate this case.

Also, could you please try another user?

Sorry for the inconvenience.


#9

5/2/2562 8:44 Starting job “Local_Tabian&TQF” - “Full”. Computer “WIN-F1IMSDQF8I1”. App v.“12.2.1”
5/2/2562 8:44 Connecting to SQL Server: 2008 R2 RTM Enterprise Edition (64-bit). 192.168.251.133
5/2/2562 8:44 The backup folder “C:\BackUpDataBase\Pranas.NET\SQLBackupAndFTP\backup” has “307.956GB” free space. The temporary folder “C:\BackUpDataBase\Pranas.NET\SQLBackupAndFTP\backup” has “307.956GB” free space
5/2/2562 8:44 Backing up “192.168.251.133” SQL Server databases
5/2/2562 8:44 Getting maximum file size…
5/2/2562 8:44 The maximum file size: “”
5/2/2562 8:44 Failed to backup “dbTQF” database with “Full” backup type: [JOB-BAK:1301#1] Failed to backup “dbTQF” database. There is no destination selected to keep Full database backup type.
5/2/2562 8:44 Failed to backup “dbtabian2558” database with “Full” backup type: [JOB-BAK:1301#1] Failed to backup “dbtabian2558” database. There is no destination selected to keep Full database backup type.
5/2/2562 8:44 Failed to backup “dbFinance” database with “Full” backup type: [JOB-BAK:1301#1] Failed to backup “dbFinance” database. There is no destination selected to keep Full database backup type.
5/2/2562 8:44 Cleaning up backups of “Local_Tabian&TQF” job
5/2/2562 8:44 Job “Local_Tabian&TQF” finished with “3” errors
5/2/2562 8:44 Sending job log to deaw6101@gmail.com
5/2/2562 8:44 Sending job log to saksun.nong.cpe@gmail.com
5/2/2562 8:44 Sending job log to jindapornongate@gmail.com
5/2/2562 8:44 Sending report to ${https://sqlbackupandftp.com/weblog?jobid=5fcf3d5b-ed07-400a-a669-e494e1729c91|Web Log}

how to backup database


#10

Hello,

Could you please check your backup destination settings, have you specified your destination like an “Emergency destination”?

Please specify another one (not an emergency) or disable “Emergency destination” option at the “Advanced backup settings”. That should resolve the issue.

Sorry for the inconvenience.