I’m using SQL Server 2008 and running a batch file from SQL Server Agent. The batch file is located in my desktop. When I try to run the job I get an error: “access is denied.” The batch file runs successfully if I run it manually. Also, the job runs successfully if I put the batch file anywhere in the C drive or F drive and run it from Agent. Any idea why the access is denied if the batch file is in desktop? The agent service account has the sysadmin server role in SQL Server. Thanks.
Any idea why the access is denied if the batch file is in desktop?
Yes. Because it is a rotten idea. The service agent account has no rights on your personal folders and your personal folders include the desktop. And you should keep it that way.
Your personal fodlers are btw., locked also for the admin accounts etc.
If you need sql agent to do something, I strongly suggest you agree on a location for that to happen and move the batch there.
The SQL Server Agent service account clearly does not have access to your desktop folder. On Windows 8, this folder would be
%HOMEPATH%\Desktop. You can adjust the access rights for this folder so the service account has access to the folder, however I would agree with @TomTom - you should consider putting the batch file somewhere more appropriate for SQL Server, perhaps
Storing files in your user profile folder is bad in numerous ways, for instance if Windows detects corruption in your profile, it will create a new folder automatically.
Create a shortcut to the batch file, and place the shortcut on your desktop if you need frequent access to it.
sysadmin server role in SQL Server has nothing to do with access to the file system. It only affects access to items in SQL Server itself.