Take the 2-minute tour ×
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:

"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box."

Even though I have db_owner and all permissions I am getting this error. But if I select the path manually in the file name field then it allows to take backup without any error.

So why does it happen? I want this user to take backup without these error messages. (But I can't give sysadmin for the user). How can I solve it?

I already some article stating that use SQL query and sqlcmd to take backup. But I want it to work using SSMS.

share|improve this question
1  
Looks like a permission issue(not to the databases but to the file directory). Try opening SSMS as administrator and usually it works. –  DaniSQL Apr 24 '13 at 14:29
1  
Make sure that the account that the SQL Server Service runs under have access to the backup location: C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup –  RoKa Apr 24 '13 at 14:33
add comment

2 Answers

up vote 0 down vote accepted

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.

CREATE LOGIN ViewAnyThing WITH PASSWORD = 'NotTheRealPassword';
GRANT VIEW ANY DEFINITION TO ViewAnyThing;
share|improve this answer
    
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 Moschini Feb 27 at 20:01
add comment

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.

share|improve this answer
    
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 researcher Apr 25 '13 at 5:53
    
Please see more details in my thread in this link social.msdn.microsoft.com/Forums/en-US/sqltools/thread/… –  IT researcher Apr 25 '13 at 8:07
    
Please see my answer below –  IT researcher May 10 '13 at 9:41
    
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 Moschini Feb 27 at 20:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.