Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login (not windows authentication). In user roles it has all permission for almost all databases. For some database it has only datareader.

Now when I try to take backup of database using SSMS and when try to select backup destination by clicking button I get the following error:

2 Answers
2

This is not a SQL Login permission issue. Rather that SQL Server Service Account can't access the location for the backup. To fix this you need to grant access in windows to that path to the account that the SQL Server Service executes under.

So if i am running sql server "Network service" then should i give permission for C drive for user "Network service"? I tried to give the permission but error came like "Unable to save permission changes on Local Disk (C:). The inherited access control list (ACL) or access control entry (ACE) could not be built."
–
IT researcherApr 25 '13 at 5:53

If the user performing the backups is browsing to the folder you want them to backup to (especially inside a Windows User folder), you'll need to ensure the Windows user they're running under has Read and List access to all parent directories as well. For example the default user SSMS runs under is "sqlexpress", and if they have access to C:\Users\Chris\Data, that doesn't automatically give them access to C:\Users\Chris - they'll be cut off from getting to the subfolder when they attempt to browse if you don't explicitly give them access to C:\Users\Chris as well.
–
Chris MoschiniFeb 27 '14 at 20:03

Here is answer for my own question
SSMS uses "xp_fixeddrives" to list the drives in PC. Same is used by SSMS when clicking on button which is used to select backup path or browse directory.

The error is generated because "exec xp_fixeddrives" doesn't return any records when executed by a non-sysadmin account.When you run the same using user having sysadmin permission then "exec xp_fixeddrives" returns the list of drives. So it is the problem with SSMS ,and it is not a problem with any permission to the folder or account under which SQL agent services runs etc.(I have tested the same)

There are lots of other functionalities that SSMS assumes you must have sysadmin rights in order to perform actions;

most of the nodes in the Object explorer assume it, scripting of objects is one example i know of off the top of my head.

example: create a user with VIEW ANY DEFINITION and login as that user into SSMS. you'll get errors whenever you expand any nodes, when in theory they should be able to view it (that login can see the metadata via queries, of course.

You might be confusing things a bit here. The error in your question is about Windows file permissions. The solution you came up with is about creating a new user, probably under another Windows user account (perhaps your own), that just so happens to have access to these Windows files - but the answer to the problem is to ensure whatever user you want performing backups has permissions to everything all the way up the folder tree. Creating a user is not necessarily the solution and may not always solve this problem.
–
Chris MoschiniFeb 27 '14 at 20:01