3

This is MSSQL Server 2008 R2 running on Windows Server 2008 Enterprise. Service Instance is running as 'Network Service'.

I have two logins, UserA and UserB. UserA is a sysadmin. UserB is a db_owner for database 'Demo'.

I login as UserA and can take a backup of Demo, no questions asked.

I login as UserB and try to take a backup of Demo. When clicking the 'Add' button to add a destination, and then the 'browse' button, I get the following error message:


G:\Backups

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.


My question is:

After reading that folder access is based on the User Account that the SQL instance is running under (Network Service), why can the db_owner not browse to the specified folder?

Is there something I am missing?

Thanks

  • 2
    please note: sql is a language. sql-server is a product. Please ensure you tag future posts with the correct tag. Thanks! and welcome to Database Administrators - please take the tour. – Max Vernon Sep 22 '16 at 14:59
  • Are UserA and UserB using Windows authentication? – Max Vernon Sep 22 '16 at 15:01
  • Try starting SQL Server Management Studio "As Administrator" – Max Vernon Sep 22 '16 at 15:01
  • No, UserA and UserB are just SQL users. I have since found the answer from another question though. Please see below. – Colin Sep 22 '16 at 15:06
1

I searched for this error before posting and could not find a result. After posting, the answer came up as a related question:

Error while taking backup using SSMS

To quote from that question:

"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)"

One thing to add is that this only affects local SQL logins and not Windows Authentication accounts.

Not the answer you're looking for? Browse other questions tagged or ask your own question.