Scripting a Backup Process and Backup File Ownership

Background

I'm writing a Powershell script to

• Backup a database to a file on disk

• Compress and encrypt the file (7-Zip/AES256)

• Copy the encrypted backup to Amazon S3

• Delete the backup file

The script is supposed to run under a Windows account with the minimum privilege set to do accomplish those tasks, and it works great with SQL Server Express 2008R2.

However, with SQL Server 2008R2 Web Edition, the backup file is written such that only Administrators have full control of the file. While my less-privileged user can read the file, it cannot delete the file.

Question

Is it possible to configure SQL Server to write the backup file such that ordinary Users can delete it?

UPDATE

Here is the script as requested in a comment. I could not find a functional S3 console app for Windows, so wrote a basic one. Source is included below. These files are released into the public domain without any warrantee.

S3EncryptedBackup.ps1

``
#param ($SERVER = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"),
# $DB = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"),
# $BACKUPFOLDER = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"),
# $BUCKET = $(throw "Usage: S3EncryptedBackup.cmd -SERVER NameOfSqlServerInstance -DB NameOfDbToBackup"))

### DEV: Set these locally. For prod, require as parameter
$SERVER=".\YOUR_SERVER_HERE"
$DB="YOUR_DB_NAME_HERE"
$BACKUPFOLDER="YOUR_BACKUP_FOLDER_HERE"
$BUCKET="YOUR_S3_BUCKET_HERE"

### EDIT System Configuration
$S3='E:\Software\S3Backup\S3Backup.exe'
$SEVEN=""C:\Program Files (x86)\7-Zip\7z.exe`"" # Edit if 7-Zip installation differs

### For AES Encryption:
$AES_KEY="YOUR_ENCRYPTION_KEY_HERE"

### Edit the environment variables below only if the AWS keys change
$AWS_ACCESS_KEY_ID="YOUR_AWS_KEY_HERE"
$AWS_SECRET_ACCESS_KEY="YOUR_AWS_SECRET_HERE"

### Create the DB Backup

$BACKUP

Access the backups files is controlled on the Windows end, not necessarily by SQL Server. SQL Server only requires that the account the SQL Server service is run as is given read/write access to the backup directory.

If you want another account to have access to the backup directory you will have to specifically add it to the ACL of that directory, or a group that might already have the permissions.

StackExchange Database Administrators Q#25712, answer score: 3