Backup / Restore of Microsoft SQL Server with less privileged users
Creation Date: March 15, 2011
Revision Date: March 15, 2011
Product: DS‑Client (Windows)
Summary
This article discusses the configuration requirements that must be performed to backup / restore a Microsoft SQL Server with less privileged users (i.e. non-system administrators / non-database administrators). This applies to both backup set types currently supported (‘classic’ Microsoft SQL Server backup set and the VSS-aware type).
Requirements to perform backup
• To backup Microsoft SQL Server databases using less privileged users (e.g. a ‘regular user’), that user must have: the “db_backupoperator” role on the databases on the target server
The following are some additional user requirements for the backup to be successful:
On Windows VISTA and later operating systems, there are UAC related requirements (to be able to access remote registry):
• Disable UAC (User Access Control) on the target Microsoft SQL Server computer.
- OR -
• Use a domain account. When the computer is a member of a domain, remote UAC does not apply when using a domain user account.
- OR -
• Disable UAC for remote connection to the target server if you are using a local account to connect to the target server. To do this, set the registry key LocalAccountTokenFilterPolicy (DWORD) to 1. If the key does not exist, you must create it. This key is found in:
HKLM\Software\Microsoft\Windows\CurrentVersion\Policies\System
Minimum privilege requirements for user performing the backup:
• The user must be a member of SQL Server target computer’s local Power Users to be able to translate from a shared path to a local path and to create the SQL statement pointing to the local path (if applicable).
- AND -
• The user must have privileges to access remote registry (besides the UAC requirement). The easiest way is to make it a member of the Backup Operators group on the SQL Server target computer.
Requirements to perform restore
• The user must have the “dbcreator” role for the database to be restored.
• The user must be added under the “xp_cmdshell” stored procedure properties, with “EXECUTE” permissions.
• Create an “xp_cmdshell” proxy Account for the user.