How to prevent a full database backup

Question

We are in the middle of migration, a full backup has been taken and copied to the destination. On Friday, we plan to do a differential backup, but someone has by mistake taken another full backup (did not use copy_only).

We want to prevent this in the future. We can think of DENYing (as follows), but we don't know the specific login. So, how do we make sure no-one can do a full backup?

DENY BACKUP DATABASE TO [LoginName]

Answer 1

The most "normal" way to prevent database backup - simply ask persons who have permissions to backup databases, do NOT do any full backups (that are not copy_only) until migration is complete...

Answer 2

We can do "DENY BACKUP DATABASE TO [LoginName]"

As long as the Login member of sysadmin, NO DENY can stop sysadmin members. So, DENY BACKUP wouldn't hep unless following applied:

1. login removed from sysadmin server role
2. Maintain one db_owner per database.

However, as security best practice, if the user need full access on a DB, keep them as db_owner in respective database rather than making them as sysadmin. Then only you can apply DENY BACKUP DATABASE to prevent it happen in future.

Aside from that, consider db_owner (if other exists in same DB) can REVOKE DENY, Creating an alert (using message:18264) would be workaround solution.

But we don't know the specific login

With following queries you could know who can do that (unexpected backup) again, and last performed backup details:

Who can do unexpected backups:

select   
        sp.name as LoginName,
        r.name as RoleName,
        sp.sid, 
        sp.type_desc,
        'ALTER SERVER ROLE SYSADMIN DROP MEMBER  ' + QUOTENAME(sp.name) as FixCommand 
from sys.server_principals as sp
    left outer join sys.server_role_members rm on sp.principal_id = rm.member_principal_id
    left outer join sys.server_principals r on rm.role_principal_id = r.principal_id
Where   r.name = 'sysadmin' and (not sp.name like 'NT SERVICE%')

Performed backup details:

SELECT      [b].[database_name] ,
            [b].[backup_start_date] ,
            [b].[backup_finish_date] ,
            [b].[type] ,
            [b].[first_lsn] ,
            [b].[last_lsn] ,
            [b].[checkpoint_lsn] ,
            [b].[database_backup_lsn],
            --,f.media_set_id
            f.physical_device_name,
            ((b.compressed_backup_size / 1024) / 1024) as CompressedSize_MB, 
            CASE    WHEN Type = 'L'
                        Then 'RESTORE LOG '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS;  '
                    WHEN Type = 'D'
                        Then 'RESTORE DATABASE '+ QUOTENAME([database_name]) +' FROM DISK=N''' + f.physical_device_name + ''' WITH NORECOVERY, REPLACE, STATS;  '
            END as Script,
            b.is_single_user,
            b.user_name
FROM    [msdb].[dbo].[backupset] AS [b]
            LEFT JOIN  msdb.dbo.backupmediafamily f ON b.media_set_id = f.media_set_id
where  b.database_name = 'YourDatabase' --- add your database name here
order by b.backup_finish_date desc 

---

Test Case (based on comments)

CREATE LOGIN TEST_USER WITH PASSWORD = 'test', CHECK_POLICY=OFF;

USE TestDB;
CREATE USER TEST_USER FOR LOGIN TEST_USER;
ALTER ROLE DB_OWNER ADD MEMBER TEST_USER;

CREATE ROLE DENY_BACKUP;
DENY BACKUP DATABASE TO DENY_BACKUP;
ALTER ROLE DENY_BACKUP ADD MEMBER TEST_USER;  -- Implicit DENY

DENY BACKUP DATABASE TO TEST_USER; -- Explicit DENY
GO

-- Implicit/Explicit BACKUP permissions Verification 
select dp.name, p.* 
from sys.database_permissions as p
        join sys.database_principals as dp on p.grantee_principal_id = dp.principal_id
where p.type = 'BADB'
go
----------------------------------------------------------------------------------------
-- Above commands must be run by SYSADMIN, below must be from TEST_USER
----------------------------------------------------------------------------------------
Use TestDB;
Backup Database TestDB to disk = 'null';  -- as expected it fails due to DENY permissions
go

sp_helpuser 'test_user'; -- Yes, "TEST_USER" is DB_OWNER

REVOKE BACKUP DATABASE TO DENY_BACKUP; -- Implicit DENY can be revoked by same user while the user is DB_OWNER
ALTER ROLE DENY_BACKUP DROP MEMBER TEST_USER; -- Implicit DENY can be revoked by same user while the user is DB_OWNER
REVOKE BACKUP DATABASE TO Test_User -- Explicit DENY cannot be revoked by same user though user is DB_OWNER. It says, "you cannot revoke permissions yourself"
go

Answer 3

I don't think there is a way to issue a DENY permission for backups.

But there are a few roles that allow logins and users to create backups.

You could disable logins from the SA group. Anyone in this login group can do backups on any database on the server.

And disable any user that is in the db_backupoperator or db_owner groups. Members with this role can backup that database. You will need to do this for each database, they are database roles, not server-wide roles.

Please consider scripting out these users and logins before deleting them. While disabling is a lot easier to restore then dropping, a restore script is a safety net to help keep you employed and way less stressed out...

Answer 4

The least permissions required to take backup is db_backupoperator ,then comes the db_owner who can take the backup. These are database level roles. 'Sysadmin' being the server role which can take backups.

Now you would like to check the permissions your logins have. Script them all out first. Just remove above permissions till you are done with the migration. Once done grant the permissions back.