Data backup & Restore Permissions
Question
Thursday, February 9, 2017 7:49 PM
Why should performing a database backup/restore require sysadmin rights? Security is such a major issue in DBMS products, and applications such as Commvault and Netbackup are often administered by people who have no idea what SQL server is or how to properly backup a database environment. Why can't we have a restricted account/role for backup/restore purposes?? You also should not need 2 database roles to perform such an important task. A single Server role and single database role OR just a server role.
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
John M. Couch
All replies (6)
Thursday, February 9, 2017 7:51 PM
Probably because sysadmin is trusted so that any user with less than those permissions in SQL server could backup a sensitive database to disk and then copy the disk to a usb and make off with it.
Thursday, February 9, 2017 8:09 PM
Why should performing a database backup/restore require sysadmin rights?
It doesn't have too be sysadmin, but typically is for simplicity sake.
Restore: https://msdn.microsoft.com/en-us/library/ms186858.aspx
'If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo)'
Backup:https://msdn.microsoft.com/en-us/library/ms186865.aspx
'BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.'
Thursday, February 9, 2017 9:40 PM
Excellent point, Hilary.
Phil Streiff, MCDBA, MCITP, MCSA
Thursday, February 9, 2017 10:25 PM
SysAdmin is trusted, yes, but couldn't a new server level role with backup/restore rights only be a better option? Giving an account sysadmin rights gives them the power to do whatever they want beyond just backup/restore. It is also another hole in security for someone to exploit. if the role was limited to just those operations and someone was placed in that role, it would be more secure and less risk than granting sysadmin.
And yes, I realize this conversation isn't going to change anything, but it just a frustration to me. We hear daily about security, plug your holes, don't give excessive rights...etc. only to have MS themselves open a hole by saying you have to grant excessive rights to perform a basic maintenance task. Yes, its an important task, but its still a basic one.
John M. Couch
Friday, February 10, 2017 9:57 AM
Hi John,
Please correct me if I’m wrong, but as far as I’m aware, server-level backup operator role would only introduce more security risk here. Image someone with server-level backup operator role takes backup of a critical database and restore it to his server, he would be functionally equivalent to sysadmin on the production server and can view any user data.
Adding him to Deny_backup database role on specific databases is possible, but why not just add him to db_backupoperator database role on the databases that he was supposed to touch?
If you have any other questions, please let me know.
Regards,
Lin
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Thursday, May 3, 2018 3:09 PM
If you uise a tool like VEEAM you have alternatives. From the VEEAM help section on SQL backups and SQL Explorer:
The following rights and permissions are required:
- The user account that you specify for guest processing of the Microsoft SQL Server VM in the backup job should have the sysadmin fixed role assigned on that SQL Server (see the "Creating Backup Jobs>Specify Guest Processing Settings" section of the Veeam Backup & Replication User Guide for details on job configuration). This is the recommended setting; however, if you need to provide minimal permissions to the account performing backup operation, you can assign the following:
- SQL Server instance-level roles: dbcreator and public
- Database-level roles: db_backupoperator, db_denydatareader, public; for system databases:
- master - db_backupoperator, db_datareader, public;
- msdb - db_backupoperator, db_datawriter, db_datareader, public
- Securables: view any definition, view server state
- For truncation of SQL Server 2012 or SQL Server 2014 database transaction logs, this account should have the db_backupoperator database role (minimal required) or the sysadmin server role.
- Make sure that Deny log on locally and Deny log on through Terminal Services are turned OFF for the corresponding account (these can be turned on, in particular, due to group policy settings).
If you want transaction logs to be truncated, note that in case log truncation with the specified account is not a success, Veeam will try to perform it using NT AUTHORITY\SYSTEM account, so for SQL Server 2016, 2014 or 2012 make sure it has sufficient rights
As for SQL Server 2008 and 2008 R2, default settings in these versions allow for database log truncation by local SYSTEM account (however, if they were modified, make sure this account is permitted to truncate logs).
- The account you will use to access the target Microsoft SQL server where database will be restored needs sysadmin fixed role on that server. (This account you will specify when working with the Restore Wizard, as described later in this guide.)
- The account you plan to use for connection to the Windows machine (where database log backup files will be copied for further log replay) will need sufficient permissions to access the administrative share on that machine: Read and Write are minimal required. For restore scenarios, that machine is your target SQL Server. (To read more about scenarios that require log replay, refer to Planning For The Database Restore section.)
- The account used to run Veeam Explorer for Microsoft SQL Server should have sufficient permissions for the folder where you plan to export the database files: Read and Write are minimal recommended.