Protecting the SQL Server Backup folder

Problem

I want to backup my SQL Server databases to a folder, but I want to minimize who has access to the folder. In other words, I want to make sure that members of the Windows Local Administrators group don't get to the backups without intentionally trying to bypass the security. How do I do that?  Check out this tip to learn more.

Solution

If you look at the permission for the BACKUPS folder that the Microsoft SQL Server install creates, you've probably noted that it has permissions where members of the Administrators group has full control, meaning they can do anything they want. Actually, this is true of most every location, including a folder I just created off the root of the C:\ drive.

In a lot of cases the Windows Local Administrators group has inherited the permissions from a higher level folder. The solution is to break inheritance and set our own permissions. Without breaking inheritance we can only add permissions to a file or folder. We can't remove permissions, so this is a necessary step. However, to make sure you don't lock yourself out, choose to copy the existing inheritable permissions.

Before you remove any permissions, the first step I'd take is to ensure you have permission to get back and manage the folder. For instance, if all DBAs should be able to get to the appropriate folder, add the appropriate Windows group for the DBAs and give it Full Control. This allows you to modify the folder and any files contained in it as well as manage the permissions. It's this last aspect of Full Control you'll find essential. I typically grant this permission, then click OK to confirm and only then do I start removing or reducing permissions.

When I go to remove permissions, I tend to automatically eliminate the following accounts or groups:

• SYSTEM
• Authenticated Users
• Users

Note I didn't strike the Administrators group. Instead, I tend to reduce permissions.  I still grant the local Windows Administrators the ability to see what's in the folder. Unless the local security policy has been changed, they can backup the files and folders regardless of permissions, but this doesn't typically give them the ability to view what's in the folder without the use of a 3rd party product. When it comes to disk space issues and the like, it can often be helpful for an administrator to be able to see the files. We can accomplish this by granting the following permission: List folder contents. This gives them the ability to see the files, but not read from them.

Most importantly, don't forget to give Microsoft SQL Server access. It will need Modify access for the folder. This gives SQL Server the ability to read files, write to them, modify them, and delete them. In SQL Server 2005-2008R2, there will be a SQL Server User group corresponding to the instance name. In SQL Server 2012 the access will be configured based on the service's virtual account name. Check the appropriate version of Books Online for this. Here's the pattern for SQL Server 2008 and 2008R2:

Type of Instance	Pattern for Group Name
Default Instance	SQLServerMSSQLUser$ComputerName$MSSQLSERVER
Named Instance	SQLServerMSSQLUser$ComputerName$InstanceName

If you don't have the appropriate access, you'll get the following error trying to run a backup through SQL Server Management Studio:

If you get this error, you're likely missing the Modify permission for SQL Server.

And that's all there is to it. While a member of the Windows Local Administrators group could still get in, he or she would have to intentionally seek to bypass security. You can't completely prevent an administrator from gaining access, however, by making it difficult you keep out the merely curious. That's the intent of altering file and folder permissions like we did here.

Source collected from MSSQLTIPS.com