Re: OPENQUERY

From: kob uki (kob_uki_at_hotmail.com)
Date: 07/07/03


Date: Mon, 7 Jul 2003 10:25:31 -0600


Thanks Jacques!

Yeah, I have modified the call according to the article, but it is still not
clear to me WHY it is so.

"By design" doesn't help too much. :) The link is impersonating, the job is
owned by a sql server user (not windows authenticated one), the job is
invoked by one windows\user1 and the job's step gets executed under the
credentials of yet another windows\user2. SQL Server can not perform SETUSER
on windows\user2.

Another thing: osql -E -Q "exec my_sp"

returns successfull execution if my_sp doesn't exist.

annoying...

Thanks, anyhow!

"VUILLERMET Jacques" <jacquesv@no-spam.moc> wrote in message
news:3f07f585$0$13223$626a54ce@news.free.fr...
> It's by design.
>
> See : PRB: SQL Server Agent Job Fails When the Job Uses a Linked Server
and
> the Job Owner Is Not a System Administrator
> http://support.microsoft.com/default.aspx?scid=kb;en-us;811031
>
> Jacques.
>
> "kob uki" <kob_uki@hotmail.com> a écrit dans le message de news:
> uSQoD3lQDHA.3700@tk2msftngp13.phx.gbl...
> > I have an openquery select statement to a linked server.
> > A job (having this openquery) is successful only if the owner of the job
> is
> > a sysadmin. This is nuts. I mean, should it be this way?
> > The openquery runs fine from QA even if the user is not sysadmin.
> > The error of the job is Remote access not allowed for Windows NT user
> > activated by SETUSER. [SQLSTATE 42000] (Error 7410).
> > So, if the the owner of the job is sysadmin then there is no SETUSER,
and
> if
> > it is not then SETUSER... not clear. (no proxy user defined).
> >
> > Can anyone explain this?
> >
> > Thanks,
> >
> > kob_uki
> >
> >
>
>