Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Error while taking backup for a user not having sysadmin permission Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 7:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:34 PM
Points: 265, Visits: 1,714
I am accessing my sql server 2008 r2 express server 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 database.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 error like

"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 itself)
Also see my detailed discussion on this problem in msdn forum by me here
Post #1445984
Posted Thursday, April 25, 2013 3:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:34 PM
Points: 265, Visits: 1,714
Can anyone please check this error and help me.
Post #1446374
Posted Thursday, May 2, 2013 1:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:34 PM
Points: 265, Visits: 1,714
Anyone faced same problem? Or anyone know how it can be solved?
Post #1448709
Posted Thursday, May 2, 2013 2:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,232, Visits: 2,672
When clicking to the backup folder the filename for the backup is not specified and should be entered manually. You can only click to a file when this (backup-)file allready exists.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1448728
Posted Thursday, May 2, 2013 3:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:34 PM
Points: 265, Visits: 1,714
Hi Hanshi
I think you have not understood my question.Please see the the link(msdn forum link) also which i refereed in my qusetion.
Post #1448743
Posted Thursday, May 2, 2013 4:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,232, Visits: 2,672
Hi,

On the printscreen with the error message you don't have a filename entered by "Destination on disk". You do have specified a folder, but if you don't add a filename the backup filename will be the same as the foldername. You can't have a file with the same name as a folder (within the same parent folder).

This folder exists on your machine:
..\MSSQL10.SQLEXPRESS\MSSQL\Backup\
So you can not create a file with name:
..\MSSQL10.SQLEXPRESS\MSSQL\Backup


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1448746
Posted Thursday, May 2, 2013 5:26 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 961, Visits: 4,981
I took a look at your MSDN posting, and Hanshi is correct. When you're getting the error, it's because you've not specified a filename for the backup, only the folder.

It's annoying, but you need to add the filename to the end after you browse to your target folder, but before you hit OK. The 3rd post on MSDN shows the problem.

With the error, you've got:
C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup

When you don't get the error, you've got:
C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\frf.bak

That's all it is.
Jason
Post #1448768
Posted Thursday, May 2, 2013 6:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:34 PM
Points: 265, Visits: 1,714
Hi Hanshi and jason
Both of you understood my question wrongly!!!
The error i am getting is not on clicking the OK button. By deafult the path is filled with path "C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup". Also i am getting error on clicking the browser button used for selecting folder to backup.
I mean on clicking the button having three dots(...) which you can see in image in msdn post.
If you still have doubt pls reply me with doubt
Post #1448785
Posted Thursday, May 2, 2013 7:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,232, Visits: 2,672
Hi,

I'm sorry I misunderstood your problem. I tried it by doing some testing and I was able to reproduce the error.
I created a SQL account and granted all but sysadmin rights. When taking a backup by clicking through the GUI, I got the error message when clicking on button [...] to browse for a location (just as you said).

I used Profiler Trace to see what was going on and found the GUI executes stored procedure "xp_fixeddrives". The error is generated because "exec xp_fixeddrives" doesn't return any records when executed by a non-sysadmin account.

I don't think your problem can be solved without giving the login explicit "sysadmin" rights. There are ways to execute the stored procedure with sufficient rights (see: http://serverfault.com/questions/82314/how-can-i-give-a-sql-server-user-permission-to-run-one-stored-procedure-and-noth) but that will not work with the GUI.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1448811
Posted Thursday, May 2, 2013 7:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 961, Visits: 4,981
My apologies, I had the "order of events" wrong then.

Looks like Hanshi found the source of the problem, and I suspect the reason it may work when you key in the path or do it through a query window, is SQL isn't trying to run the XP_Fixeddrives.

Jason
Post #1448816
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse