Microsoft SQL Server requires specific user roles to perform specific operations. To perform Backup and DR capture, restore, unmount, delete, and virtual application mount operations on a SQL Server database, you must provide credentials for a Windows user—a local user or a domain user—who has a role with sufficient SQL privileges to perform the operation.
This section details the user roles required to perform capture, restore, unmount, delete, and virtual application mount operations from a backup/recovery appliance. The recommended roles presented in this section are based on Microsoft's best practices for accessing SQL Server databases.
Before you begin, it's a good idea to review Backup and DR for SQL Server databases.
Windows local administrator user
To perform capture, restore, unmount delete, and virtual application mounts, the Backup and DR agent must be installed with the credentials of a Microsoft Windows user who has sufficient privileges in the SQL environment. The Windows user must be assigned a specific role or roles. The Microsoft Windows user can be a newly created or existing user.
Required SQL roles for the Windows user
A Windows local administrator user assigned to the sysadmin server role has all
necessary permissions to perform Backup and DR capture, restore and
virtual application mounts.
If the sysadmin server role is deemed too liberal, then assign a
Windows user the following roles:
dbcreatorserver roledb_backupoperatordatabase roledb_ownerdatabase role
In addition, such users must also be assigned the following securables:
View any database
Create any database
Alter any database
Connect SQL
The following sections detail where to enter the Windows local admin's username and password to perform specific Backup and DR SQL related operations.
Credentials for capturing SQL Server database logs
When applying a backup plan policy template to a SQL Server database, if the template contains a policy that captures database logs you must enter credentials of a Windows user assigned the proper roles in the management console in the application's backup plan application details and settings.
Credentials for restoring a Microsoft SQL Server database
When restoring SQL Server databases from the management console, in the Restore dialog, enter credentials of a Windows user assigned the proper roles.
Credentials for mounting a SQL Server database as a virtual application
A virtual application mount mounts a SQL Server database as a virtual application. When performing a virtual application mount of a SQL Server database from a backup/recovery appliance, the user must be assigned a role that allows both the ability to mount and unmount—detach—the SQL Server database.
When performing a virtual application mount, in the Mount dialog Advanced options, enter the credentials of a Windows user assigned the proper roles.
The Backup and DR SQL Server DBA guide
This page is one in a series of pages specific to protecting and recovering Microsoft SQL Server databases with Backup and DR. You can find additional information at: