Error while taking backup for a user not having sysadmin permission

  • 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

  • Can anyone please check this error and help me.

  • Anyone faced same problem? Or anyone know how it can be solved?

  • 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’! **
  • 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.

  • 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\BackupSo 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’! **
  • 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

  • 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

  • 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’! **
  • 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

  • Hi Hanshi

    Yes, you are correct. I was knowing it that SSMS uses xp_fixeddrives. I have also included it in my msdn forum link.But I was not sure about it.

    But if the query is able to take backup or if we give directly file name then backup works.So it is not a permission problem.Only problem is with how SSMS works. Also a small bug in SSMS!!

  • 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;

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Under what account does the SQL server and SQL agent services run?

    Those accounts must have modify access to the path.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply