1

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]
4

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

  • 2
    This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review – mustaccio Nov 12 at 15:05
  • @mustaccio this is just your personal opinion. This is the best answer because it is straightforward and sometime you have just to talk to people instead of inventing some perverted solution – Aleksey Vitsko Nov 12 at 15:26
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

  • 1
    I think this is the best possible answer to the question. – James Jenkins Nov 7 at 12:31
  • This answer is not correct in the part "no deny can take precedence...". Deny ALWAYS has precedence, but in the case of sysadmin the permissions ARE NOT CHECKED AT ALL, this is why you see that deny does not work. In case of db_owner deny WILL WORK, but every db_owher can revoke this deny, even if it will be not a direc revoke. Besides, you cannot deny backup database using DENY BACKUP DATABASE – sepupic Nov 7 at 16:37
  • Thanks for that clarity, so grammatically i should call it DENY CANNOT STOP sysadmin not to use precedence, although, in-practical eventually both result same. to keep answer summarized removed the db_owner reference (will revert back previous version) – Shekar Kola Nov 7 at 18:25
  • @sepupic, could you pls elaborate, you cannot deny backup database using DENY BACKUP DATABASE – Shekar Kola Nov 7 at 18:29
  • 1
    @Shekar Kola Of course any version above will support this, every new version brings more granular permissions and what you could achieve in previous versions only by assigning to a role now you can grant/deny explicitely. I'll upvote your answer as this is really the point: you can now deny it without messing with the roles – sepupic Nov 12 at 10:55
1

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.