Why can't I read from .BAK files on my Desktop using SQL Express in Windows Authentication Mode

Question

I am trying to execute this SQL query prior to restoring a .BAK file in SQL Express. Initially, I had the file on the Desktop of my user account. I am logged in as Administrator.

When I try to access the .BAK file on the desktop like this

RESTORE FILELISTONLY FROM DISK= 'C:\Documents and Settings\Administrator\Desktop\file.bak'

I get an error.

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Documents and Settings\Administrator\Desktop\file.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

However, when I move the .BAK file to C:\temp, and execute this

RESTORE FILELISTONLY FROM DISK= 'C:\temp\file.bak'

It works just fine.

I cant figure out what is going on. Is there a way to access files on Desktop using Windows Authentication with SQL Express?

Answer 1

Try granting read permission to Users or LOCAL SERVICE to the folder C:\Documents and Settings\Administrator\Desktop\ in your Policy Editor (right click the folder and in the Security tab click Add... then Advanced...|Find Now

Answer 2

When you are running the restore script, it is executing under the context of the service account that the SQL Express is running under. Go to the Services Management Console ( or run Services.msc) and find the SQL Express service and look at what account it is configured to run under and then look at the permissions of the file on your desktop and make sure that account has read access.