I have sql 2000 job that has been setup as a "Operating System Command (CmdExec) job. I am logged into the SQL Server as DomainName\SQLAdmin this domain account is part of the Administrator group on the SQL Server that the job in running on. This account has SysAdmin rights and is also starting the MSSQLSERVER Service and SQLSERVERAGENT Service on the same machine.
The job just copies files from one directory to another, here's the code.
D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d
When run as a job, with the owner of the job being DomainName\SQLAdmin the job fails with the following error message: Executed as user: DomainName\SQLAdmin. The process could not be created for step 1 of job 0x822E9AD29DCAAF4196369A46C7FE212A (reason: Access is denied). The step failed.
Here's the weird part if I open a command window on the sql server and run the batch it works fine.
I even tried executing the commands via xp_cmdshell but that didn't work either, I recieved the message: (1 row(s) affected) with the output being NULL and the file was never copied.
Anyone have an idea whats going on?It sounds like there could be multiple reasons for the access denied issue, but an odd one which I just found out about follows:
I was just trying to restore a db and I was receiving an Access denied error. I ended up finding out that some of the parent folders were in 'read only' mode. After I disabled it, everything worked fine.
Good luck,
Hope this helped|||what account is listed as the job owner?|||The account that owns the job, DomainName\SQLAdmin, is also the account that belongs to the Administrator group on the server and Stops and Starts the MSSQLSERVER/SQLSERVERAGENT services. As far as I can tell this account has all the permissions possible.
Also checked permissions on all parent folders none are marked read-only.|||I found a work around but it doesn't explain why my original job failed. What I did was create a batch file with the following code.
D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d
I then stored it on the C: drive and called the batch from within the job and this worked. So anyone have an idea what's going on? I'm sure it's a permissions thing but I can't explain it.
No comments:
Post a Comment