Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error while taking backup for a user not having sysadmin permission


Error while taking backup for a user not having sysadmin permission

Author
Message
IT researcher
 IT researcher
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 1798
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
IT researcher
 IT researcher
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 1798
Can anyone please check this error and help me.
IT researcher
 IT researcher
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 1798
Anyone faced same problem? Or anyone know how it can be solved?
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 3626
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’! **
IT researcher
 IT researcher
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 1798
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.
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 3626
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’! **
jasona.work
jasona.work
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1881 Visits: 10900
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
IT researcher
 IT researcher
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 1798
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
HanShi
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 3626
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’! **
jasona.work
jasona.work
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1881 Visits: 10900
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search