Tell me more ×
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.
up vote 3 down vote favorite

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 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 at 14:33

2 Answers

up vote 5 down vote

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 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 at 8:07
Please see my answer below – IT researcher May 10 at 9:41
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

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.