Problem
Sometimes there is a need to move SQL Server files or write backups to different drives and or folders. To make sure the SQL Server service account has access you might need to grant permissions to these new file locations. In this article, we will look at the steps to do so.
Solution
For this example, let’s say we need to create a new folder for database backup files. You can create the folder anywhere on the machine. After creating the folder, you need to give the SQL Server instance service account permissions to the folder, below are the steps.
This same process could be used if you want to use a different folder for database data and log files.
Creating a Folder and Grant Permissions
First create the folder, for this example we are creating the folder C:\Backup.
Next open SQL Server Configuration Manager to get the account used for the SQL Server service.
Double click on the SQL Server name to open the Properties window. On the Log On tab copy the Account Name as shown below.
Now, navigate to the folder you created and right click on the folder and select Properties.
Once on the Properties window (Backup Properties for our C:\Backup folder example), click the Security tab and click on the Edit button.
On the next window, click the Add button.
Now, paste the SQL Account Name you copied from the steps above in Enter the object names to select area and click the Check Names button.
This will show you the SQL Account Name as stored on your system. After you see the Account Name, click OK.
After adding the user, while the user is selected, check the Full control box and click Apply to make the change then press OK to close the window.
Great! You have created the folder for your backups and gave your SQL account user full control to this folder.
Next Steps
- Check out these other related SQL Server Security Tips
