Restricting users to COPY ONLY backups

Question

I am looking to deploy log shipping in my production environment. This will mean I will have to manage backup chains which could be disrupted by people taking backups to refresh development environments. If I have a failure and want to use the log backups to restore to a point in time I will also need the backup taken by the developer.

This wouldn't happen if the developers only used COPY ONLY backups. So my question is: Is there a way to restrict users to only be able to perform COPY ONLY backups?

Answer 1

You dont need to have them use COPY_ONLY. Only An intermediate LOG BACKUPS will break the LSN. What you can do is explicitly DENY BACKUP LOG to [user|group] privilege to developers or developer group. Alternatively, just create a ROLE and deny backup log to that role. So all the users in that role will inherit the permissions.

e.g.

USE test_kin
GO
CREATE ROLE [deny_log_backups]
GO
USE [test_kin]
GO
CREATE USER [Kin] FOR LOGIN [Kin]
GO
ALTER USER [Kin] WITH DEFAULT_SCHEMA=[dbo]
GO
use test_kin
GO
DENY BACKUP LOG TO [deny_log_backups]
GO
USE test_kin
GO
EXEC sp_addrolemember N'deny_log_backups', N'kin'
GO

Now test it :

backup database [test_kin]
to disk = 'C:\crap_test\kin_test_full.bak'
with compression, stats =10, init

---- ### success for FULL BACKUP 

backup log [test_kin]
to disk = 'C:\crap_test\kin_test_log.log'

 --- $$$ ERROR MESSAGE 

Msg 262, Level 14, State 1, Line 3
BACKUP LOG permission denied in database 'test_kin'.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.

Answer 2

Good question that I can't find a good answer for, but here's one. What if you removed everyone from having the backup role and then created a task that used the execute as command for a specific user that drops the copy_only backup file out to a specific location?

Answer 3

What you might be able to do is 1) deny backups (log and/or full) to such users but still 2) allow them to start a job that perform copy-only backup of a specific database (running under an account that is granted backup permission). I have such a solution in place to automate a PROD to PRE-PROD automated restores of databases for investigation purposes; specific users have access to a job (in PRE-PROD) that does a copy-only backup in PROD, moves the file from PROD to PRE-PROD and then restore the moved backup on the PRE-PROD server. Thus making sure such users have no way to break the backup logic on PROD.

(When investigating issues, developers might be tempted to take a full backup to restore it on a test server by actually moving the file from source to test server without knowing that they are breaking the backup logic... so I agree that forcing taking copy-only backups is important.)