View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.
If you're just setting up a brand new computer like I am, you may have experienced this issue before. When you try to restore a backup file or attach a database in SQL Server Management Studio, you may be unable to browse to the location of the .bak or .mdf file if it is located in one of your personal folder or another similarly secured folder, as seen below. Keep in mind I am using Windows 7 and SQL Server 2008 R2.
While the folder that contains my backup file is located within the folder sharrison, it appears as if there is not. This is because the SQL Server account does not have permission to reach the folder you are trying to browse. To give your SQL Server account access to this folder, we first have to verify the account used to login to SQL Server. Open SQL Server Manager Configuration Manager and navigate to SQL Server Services. Right-click SQL Server (MSSQLSERVER) and select properties. On the Log On tab, you should see where the account to login as is specified.
In this case, its my Network Service account. Now I simply need to give this account permission to access this folder. To do this, navigate to the folder you wish to give permission to access and right-click the folder. Select Properties and go to the Security Tab. Click Edit and then click Add to add a user. After typing in the name of the user I want to give permission to access the folder, I can click Check Names to verify the user name.
Click OK a couple times and you are done! You may or may not see a pop up box saying "An error occurred while applying security information to: ". Just click Continue as many times as you need to. The changes should still be applied, according to Microsoft. Now I can browse to the folder where my database or back up file is located!
Other posts by DustinRyan