SQL Backup file permissions

SQL will, using SSMS, SQL Agent or a Maintenance plan, backup files to a directory.  Default that location is X:\Backups.  Whatever.  You can specify it when you install.  Depending on where the location is, you might have a number of different security accounts with permissions.  Effectively by default the Admiistrators Group will have full control, as well as system.  MSSQL tips has an article on this topic (http://www.mssqltips.com/sqlservertip/2768/protecting-the-sql-server-backup-folder

Users, Authenticated Users and System do not need access.  Assuming you are removing them, you probably have to stop inheritance from parent directories (otherwise you can add but not remove permssions), and for safety, “convert to explicit” when breaking inheritance.  Then remove what you want.

The author suggests reducing the administrator group permissions down to “read” so that they can view the properties of the backups, but not mess with them.  Of course, an admin can always take ownership if necessary.  

Depending on the version, the critical account is SQLServerMSSQLUser$ComputerName$MSSQLSERVER – which has to have modify in order to run.  Incorrect permissions will generate an error on backup by SSMS so you can check on what is working.  

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.