Question & Answer
Question
What are the minimum permissions needed to backup and restore self-contained application data for Microsoft SQL Server?
Cause
The Tivoli Storage Manager self-contained application protection can be configured using the Tivoli Storage Manager for Virtual Environment following the instructions from the manual, but the Tivoli Storage Manager for Virtual Environment user guide doesn't specify the permissions needed from the Microsoft guest user that will perform the backup/restore operations.
Answer
Backup rights:
Users with the db_backupoperator database role are granted to run the self-contained application data backup.
If the user is a member of the SQL Server sysadmin fixed server role, he can back up any databases of Microsoft SQL Server instance. The user can also backup the databases for which he is the owner and if he doesn't have backup rights to a specific database.
Restore rights:
If the database already exists, the user can perform the restore if he is a member of the dbcreator fixed server role, or if the user is the database owner.
Users with Microsoft SQL Server sysadmin fixed server role, have rights to restore a database from any backup sets. For other users, the situation depends on whether the database already exists.
| Minimum backup rights | Minimum restore rights | |
| sysadmin Server-level role or db_backupoperator Database-Level role or dbo_owner Database-Level role | sysadmin Server-Level role or dbcreator Server-Level role or db_owner Database-Level role |
MS SQL roles : Database-Level Roles and Server-Level Roles
In addition to the requirement described from the table above, Permissions to backup and restore will need to be granted using one of the following methods:
- Use a local built-in administrator account
- Disable the User Account Control (UAC) security component. This method requires you to access the User Account Control Settings dialog in your Windows system Control Panel, and move the slider to the Never notify setting
- Disable the Admin Approval Mode security policy setting. This method requires you to access the Local Security Settings dialog on your Windows system and disable the User Account Control: Run all administrators in Admin Approval Mode policy setting.
After the UAC has been disabled, the Windows system must be restarted for the change to take effect.
Related Information
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg21647995