Who Can Do Backups? - 25 Feb 2008

Not everyone can back up a database. This permission is limited by default to members of the sysadmin fixed server role, and to members of the db_owner and db_backupoperator fixed database roles. If you’re using backup devices, whether disk or tape, you need to be careful about ownership and permissions. SQL Server must be able to read and write to the device. If the account under which SQL Server runs doesn’t have permission to access the device, you won’t know until the backup or restore job fails. The stored procedure sp_addumpdevice, which adds a device entry to the system tables, does nothing to check access permissions at the file level.

You can specify a password for a backup set. If you do so, then whoever is restoring the database must supply this password before the restore operation will work. Password-securing a backup is an optional feature in the backup command; this feature provides a level of protection that’s considered weak. Password-securing is meant to prevent incorrect restores by someone who isn’t authorized or by someone who isn’t familiar with the backup and restore policies you have in place. Because backup data sets aren’t encrypted, this option doesn’t stop someone from reading the backup file with specialty software. A password won’t stop you from overwriting or deleting a backup file.