SQL Server Backup Permissions

backup permissionsBackup permissions: This post will take you through the list of backup permissions a user needs to have in order to be allowed to make the most basic backup operation.

We will also explain what are some of the most advanced options for backing up databases and how you can give these privileges as well to some of your most trusted users.

In this article we will talk about the minimum required permissions in order for users to take database backups of SQL Server databases as well as more extensive permissions which give access to more important tools and commands.

Minimum backup permissions

If you want to create a user and give him minimum permissions in order to just backup the database, without being able to read or modify the data in the database, here is how you have to configure this user.

When setting the Login Properties for the user make sure that in the Server Roles section, public is checked (it should be automatically).

backup permissions

In the User Mapping menu section which gives you access to  the Database Level permissions, make sure you select the database you want to give the user backup permissions to.

databaselevelroles

After doing so, make sure you check the following list of roles for the user you’ve selected, on the database you’ve selected in order to give him just backup permissions:

  • db_backupoperator – This role will allow the user to take backups of the database
  • db_denydatareader – This role will deny the user access to the database’s data, so he cannot read the data from its tables
  • db_denydatawriter – This role will deny the user access to modify the database’s data, so he cannot run any UPDATE or DELETE queries
  • public – Every user that is set up on the database is part of the public database role. When a user is denied access permission on a securable object, that user will inherit the permission to public  for that object.

About Radu Gheorghiu

Passionate SQL Developer on the journey of trying to become an expert in all things Data (storage, manipulation, gathering etc.) Information is power, and information can only be obtained from data. Thus, in order to harness the power of information, you must be a master of Data.

1 thought on “SQL Server Backup Permissions

Leave a Reply

Your email address will not be published. Required fields are marked *