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 1 down vote favorite

I'm facing a strange problem. When I try to backup or restore any database using a SQL Server user (not sa) that belongs only to "public" server role, i have the following error when i click the browse button on the "Select backup destination" window:

D:\SQLServerData\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. ...

The user is db_owner for the database I try to backup, and SQLServer service is running with administrator account, so it should have no problems with access to the D:\SqlServerData\ folder.

I tried to add the user to any other server role, but none worked except "sysadmin". But having the user in sysadmin server role means that the user can do anything in any database, and I do not want that.

Any idea on what's going on?

share|improve this question
1  
If you do the backup via TSQL only? – gbn Nov 9 '11 at 17:00
The point is that the login 'xyz' is given to users that use SSMS to operate on their databases, and they must be able to use SSMS to do backups/restores. – Claudio Valerio Nov 10 '11 at 18:58
1  
I said "If you do the backup via TSQL only" because it may be easier to give a stored procedure to wrap the backup to users. I wouldn't let the choose their own path. Now, are you going to answer it or not? – gbn Nov 10 '11 at 19:02
Yes, backup via TSQL works. Another thing that works is specifying the file name for the backup, instead of clicking the browsing button. I think SSMS uses some store procedure to retrieve disk information, maybe i simply have to grant execute permission on that stored for the login 'xyz'. But which SP could it be? I'll do some test with an analyzer, maybe i can get some hint... – Claudio Valerio Nov 12 '11 at 12:01
Ok, got one hint. The browse button in backup / restore wizard uses master.sys.xp_fixeddrives to retrieve drives list. I think this could be part of the problem because if login 'xyz' does not belong to sys_admins, xp_fixeddrives returns an empty set. Note that user has permission of executing that stored, because there are no permission errors. – Claudio Valerio Nov 12 '11 at 12:57

1 Answer

up vote 1 down vote

Most likely the problem is that the Windows or ADS account the is the service account for the SQL Server Engine doesn't have access to the directory mentioned. Give that user (note that this is not going to be a SQL Server user) access to read/write to the directory and your problem should be resolved.

share|improve this answer
Hi Redden, thanks for your answer. The service account under which the SQL Server service is running is Administrator, so it has full permission on the entire disk! (otherwise i think neither sysadmins server role members would be able to view the disk content, right?) – Claudio Valerio Nov 10 '11 at 10:06

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.