Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
adamak
Starting Member
7 Posts |
 Posted - 2003-04-16 : 10:46:34
|
| I have read Q269074 already, but I still cannot get DTS pkg to run under a job. The package itself works fine if I run it manually.I'm trying to copy 1 table and 1 view (from same db) from ServerA to ServerB. Both SQL2000 SP2. Both SQL server services are started by the same domain admin account. SA password are different. The DTS Package says drop destination object first, and then copy over & replace data. The job is owned by that domain admin account (that start both SQL servers). But I have also tried with SA owner and still failed.The error is-DTSRun OnError: Copy SQL Server Objects, Error = -2147024891 (80070005)Error string: Access is denied.Why wouldn't the domain admin acct have access to copy a table? I checked SQL Login, and it's listed under LOGIN, with PERMIT. So, what other access is needed? |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-04-16 : 11:04:07
|
| The important thing is the SQLAgent service.When call DTSRun.exe in you job step, it is doing a xp_cmdshell to run that .exe. By default, this cmdshell will run under the credentials of the user who owns the SQLAgent service.Jay White{0} |
 |
|
|
adamak
Starting Member
7 Posts |
Posted - 2003-04-16 : 11:29:42
|
| Both servers' SQLAgent runs under the same NT Domain Admin account, that also start both MSSQLServer. So this domain acct should have rights to everything.What about the owner of the DTS pkg? Does it matter if that is a different acct? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 15:13:49
|
| Can you run ANY DTS Package from a scheduled job? I'm think that domain admin account doesn't have rights in the database your working against.Brett8-) |
|
|
|
adamak
Starting Member
7 Posts |
Posted - 2003-04-17 : 15:42:51
|
| There is another old DTS job, against a different database, which runs successfully (copy and insert data, not table).If I create a test DTS pkg to copy a Northwind table from other server to this one, it gave me the same Access Denied error. The domain admin acct is listed with PERMIT under Security.... Where else can I check permission? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-17 : 16:21:12
|
| I would imagine that you have to check the database user and roles. Just guessing here. I'm only familiar with SQL Server level security (which from what I've been reading is not a good thing).Anyway it's worth a shot.Brett8-) |
|
|
|
adamak
Starting Member
7 Posts |
Posted - 2003-04-21 : 11:28:35
|
| I fixed it. Really strange, from within the package, there is an option "Script File Directory". It was pointing to C:\Program Files\Microsoft SQL Server\80\Tools. When I change this path to C:\Temp (or anywhere else), the job works.I still dont' exactly understand why it cannot write to the path. The domain that starts SQL Server & Agent is a domain and local Admin account, which has rights to C drive. I have also added this acct to the database as DBO. I got CreateObject Access Denied (to create ServerName.DBName.Log file), until I changed the path.Thanks everyone for the input. |
|
|
|
|
|
|
|
|
DTS job failed but pkg ran OK manually