Backup 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).

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.

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 the public for that object.