Unable to export a dump of MSSQL database: BACKUP DATABASE permission denied (Error code 21)

Symptoms

Unable to export a dump of MSSQL database at Plesk > Domains > example.com > Databases > example_db > Export Dump . The following error appears:

PLESK_ERROR: Unable to export a dump of example_db:
Unable to backup database 'example_db'
BACKUP DATABASE permission denied in database 'example_db'.
BACKUP DATABASE is terminating abnormally.
(Error code 21)

Cause

Insufficient permissions for a default database user.

Resolution

Correct permissions for a database user:

1. Check which users have read and write access to the database in Plesk > Domains > example.com > Databases > User Management (including users for `Any database`).

2. Log in to SQL Server Management Studio as a privileged user (e.g. sa, Administrator) and check permissions/roles for database users from the previous step: expand ServerName\MSSQLSERVERXXXX > Databases > example_db > Security > Users > right click on user to open its properties > Membership > check the missing Roles > OK .

   A default set of permissions is:

   • db_backupoperator
   • db_datareader
   • db_datawriter
   • db_dlladmin

Alternately, create a new database user for the affected database and set it as default:

1. Go to Plesk > Domains > example.com > Databases > User Management > Add Database User .

2. Fill out the fields, set Database example_db and check the box Use this user's credentials by default for accessing myLittleAdmin .

3. Click OK to apply the changes.