How To Deny Backup to Database Owners in SQL Server

When you authorize logins as db_owner in the database, your disks may be full at an unexpected time. Because a login who has the db_owner privilege can backup his/her database. This will cause security vulnerability.

For example, let’s talk about the risk that is related to our article. If the application developer wants to backup the database that he/she is the owner of, he will see several drive names as the backup location. These drive names are actually the disks on the server where sql server is located. Think that, the application developer starting backup assuming these drives are his/her own local disks. Backup can fill the database disk or operating system disk.

If the disk that the application developer selects as backup location is the database disk, the database may become unavailable. If he/she selects the backup location as operating system disk, it may even crash the operating system.

Therefore, instead of granting the owner rights in the database to the application developers, you can create a database role and grant the minimum privileges to this role. You may want to read the following articles about these topics.

“How To Create Database Role in SQL Server“,

“How To Check User Privileges in SQL Server”

I would recommend that you perform the above-mentioned actions instead of denying backup to db_owners. But if you still want to grant db_owner privilege to “Login”, and deny backup, you can follow the steps below.

First, right-click the relevant database and click Properties. You will see a screen as follows. From this screen, click on the Permissions tab and click on the user to select Deny from the “Backup database” section you see below and click ok.

After deny operation, when this user tries to create a backup, he/she will get the error as follows.