sql server programming: Hi, I get access denied errors when trying to do a backup or restore to
a path based on the temp folder returned from the GetTempPath API. For
instance:

Cannot open backup device 'C:\Documents and Settings\user.DOMAIN\Local
Settings\Temp\Asc\db_backup.bck'. Operating system error 5(Access is
denied.).
BACKUP DATABASE is terminating abnormally.

This does not happen with other arbitrary paths. Why would SQL Server
2005 Express act this way on the system defined temp folder? Is there a
way around this other then defining your own temp folder?

jonpb (nospam@nospam.com) writes:
[quoted text, click to view]

First thing to check is whether there already is a file there with that
name. Next is to check whether this file may be open by another process.

Else, it's apparently a permissions issue. You will need to examine the
permissions of the file (if it exists), and the directory. Next is to
examine under which account SQL Server runs under. Use SQL Configuration
Manger to determine this. Alex Danger suggested that you should use
LocalSystem, and while this may resolve the issue, I would not really
recommend it. It may be better to change the permissions for the directory.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

The account that sql server runs under probably does not have
permission to use that particular folder. Try opening up the services
dialog (type services.msc at the command line), and find the service
'SQL Server (SQLEXPRESS)' or something similar. If you click on
properties and then the logon tab, it will list the account that sql
server runs under - probably named 'NT Network Service'.

So now you can simply change the sql server logon to 'local system'.
You might also have to tick the 'allow service to interact with
desktop' button. This should allow the backup/restore procedure to
access the files because it is running as a normal application.

If security is an issue for you, maybe you should investigate creating
a special account for sql server to run under, however I am not
knowledgeable enough to go into those details.



[quoted text, click to view]