如何防止完整的數據庫備份

我們正在進行遷移，已進行完整備份並將其複製到目的地。週五，我們計劃進行差異備份，但有人錯誤地進行了另一個完整備份（沒有使用 copy_only）。

我們希望在未來防止這種情況。我們可以想到DENYing（如下），但是我們不知道具體的登錄。那麼，我們如何確保沒有人可以進行完整備份呢？

DENY BACKUP DATABASE TO [LoginName]

防止數據庫備份的最“正常”方法 - 只需詢問有權備份數據庫的人員，在遷移完成之前不要進行任何完整備份（不是 copy_only）…

我們可以做“拒絕備份數據庫到

$$ LoginName $$"

只要登錄會員sysadmin，NO DENY就可以停止sysadmin會員。因此，DENY BACKUP除非應用以下內容，否則不會 hep：

1. 從sysadmin伺服器角色中刪除登錄
2. db_owner每個數據庫維護一個。

但是，作為安全最佳實踐，如果使用者需要對數據庫的完全訪問權限，請將它們保留db_owner在各自的數據庫中，而不是將它們作為sysadmin. 然後只有您可以申請DENY BACKUP DATABASE以防止將來發生。

除此之外，考慮db_owner（如果同一數據庫中存在其他）可以撤銷拒絕，創建警報（使用消息：18264）將是解決方案。

但是我們不知道具體的登錄

通過以下查詢，您可以知道誰可以再次執行此操作（意外備份），以及上次執行備份的詳細資訊：

誰可以進行意外備份：

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%')

執行備份詳細資訊：

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 

---

測試案例（基於評論）

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

引用自：https://dba.stackexchange.com/questions/252699