Re: 2 Urgent Problems need to solve
From: Andrew John (aj_at_DELETEmistrose.com)
Date: 08/31/04
- Next message: Itzik Ben-Gan: "Re: Performance of Like in the Query"
- Previous message: Andrew John: "Re: critical section in tsql"
- In reply to: Devil Garfield: "2 Urgent Problems need to solve"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 31 Aug 2004 16:06:33 +1000
Devil Garfield wrote:
> Dear Experts,
>
> Case description:
> At the local server (Server A), I have created a stored procedure (SP) to
> get the data from 2 oversea servers (Server B and C) and insert the result
> data in a table of Server A; So I create a scheduled daily job to run that
> SP, but I found that it failed on execution. Firstly I supposed it's the
> error on the SP, but it works perfect (??)while I run the script (in SP) at
> the SQL Query Analyer.
>
> Question:
> 1. Is the SQL Server don't permit user to use a SP to get the data from
> another server & insert data into the table on the local server ??
>
> 2. How can we trace the error which occur on the job execution ??
> (Because I just see the this on the job history - <The job failed. The Job
> was invoked by User sa. The last step to run was step 1 (Update Table).>)
>
> Thanks a lot & a lot for all of your concern & helping !!!
>
> Best Regards,
> Devil Garfield
You (or your windows account) are probably an administrator on all the
servers, so your windows credentials get you mapped to "sa" on the
overseas servers.
The SQL Agent service may be running in a different account, and that
account is not sufficiently privileged on the remote servers. Hope not
anyway or your security is a bit of a worry.
Assuming you are using integrated security, I have found that logging on
to the local machine, with the account that SQL Agent is running under,
and testing in Query Analyser is a good diagnostic tool. If it works
there, then it normally works in a SQL Job.
It's also normal and sane, for the remote server admin to have granted a
specific restricted account access to only a specific stored procedure
in a specific database.
If you provide more complete information you will get a better answer:
a) Integrated Security ?
b) Accounts that SQL Server and SQL agent are running under
c) Linked Servers or openrowset ?
d) How the servers are linked - there are several options
e) Database access / Object Permissions granted to the mapped user ?
f) How the NT security is linked - same domain, trust ...
g) The text of the stored procedure
...
Or check Books Online security topics.
Regards
AJ
- Next message: Itzik Ben-Gan: "Re: Performance of Like in the Query"
- Previous message: Andrew John: "Re: critical section in tsql"
- In reply to: Devil Garfield: "2 Urgent Problems need to solve"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
