Re: schedule job - login in dynamic sql

From: Quentin Ran (ab_at_who.com)
Date: 03/23/04 

Date: Tue, 23 Mar 2004 14:55:35 -0600

Kriste,

a few userids are involved here.

(owner of the stored proc: this part can be skipped if you are not playing
too much with the schema. If the user created the stored proc without
specifying proper ownership, other users will not be able to execute it.)

You have a userid as the login on the server. You login and run the stored
proc which involves a linked server. Here you have to have given your login
the access to the linked server.

When you run the same stored proc from the server by a job, the user who
runs the job and also then the stored proc is the userid under which SQL
Server Agent Service runs. So you need to give exec permission of the
stored proc to the Agent userid (normally not needed explicitly, since
usually the Agent userid is a member of the sysadmin, or at least of dbo),
then you need to give this Agent userid access to the linked server, then
you need to give this agent userid access to the objects in the db on the
linked server you are accessing.

Since mostly the server agent userid is a domain user, and if the security
is set up as the mostly used default, things should work. If not, you need
to check this line of security. Where the permission is not set right, it
will break. You can test this step by step, use the agent account, starting
from local and extend to the linked server.

Quentin

"kriste" <kriste_l@hotmail.com> wrote in message
news:ukRnkqOEEHA.1544@TK2MSFTNGP09.phx.gbl...
> Can someone pls help me out on this?
>
>
> "kriste" <kriste_l@hotmail.com> wrote in message
> news:uhl8NHKEEHA.3016@TK2MSFTNGP11.phx.gbl...
> > Hi Greg,
> >
> > This is how I setup the link server.. I create the link server @ SQL-EM
> and
> > entered the id/password.
> > At the query, I was able to call up and access the link server.
> >
> > Eg. Server A, link server B.
> > I have a job in server A where it includes 2 steps, on succeed of step
1,
> > step 2 that use the
> > dynamic sql (embedded with link server B) will be run. The problem now
is
> I
> > have id/password problem in running step 2.
> >
> > I don't understand what do u mean by openrowset. This is a sp and not
> > calling from program to do updating.
> >
> >
> > "Greg Obleshchuk" <greg-n-o-s-p-a-m-@ag-s-o-f-t-w-a-r-e.com> wrote in
> > message news:%23kaZbgJEEHA.3256@TK2MSFTNGP09.phx.gbl...
> > > Hi,
> > > The schedular is not doubt run with a different password than your
user
> > (via
> > > QA). You link server sounds like it is set-up to pass through current
> > User
> > > ID.
> > > You can set up the link server to use a single User ID and password
but
> > that
> > > would mean that anyone using that linked server would have the same
> access
> > > (Which might be update and something you might not want)
> > >
> > > You could always try using openrowset, which allows you to pass the
USER
> > ID
> > > and password through to the other server and doesn't require a linked
> > server
> > > entry.
> > >
> > > regards
> > > Greg
> > >
> > >
> > > "kriste" <kriste_l@hotmail.com> wrote in message
> > > news:#yo1GgIEEHA.3424@tk2msftngp13.phx.gbl...
> > > Hi,
> > >
> > > I've created a sp that used dynamic sql and embedded with linked
server
> > > name. It was tested working @ the query analyser.
> > > But when that was placed in the schedule job to be run, it give error
> that
> > > login/password was incorrect.
> > >
> > > Both my servers maintained difference set of login/password. Can
someone
> > > highlight me how should I set the login/password that sp in the job?
> > >
> > > thx.
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: schedule job - login in dynamic sql
    ... If the user created the stored proc without ... > proc which involves a linked server. ... > runs the job and also then the stored proc is the userid under which SQL ... > usually the Agent userid is a member of the sysadmin, or at least of dbo), ...
    (microsoft.public.sqlserver.programming)
  • Re: WSS: Finding My Sites through SQL
    ... sites on a diferent server than the WSS server. ... foreach (XmlNode node in AllSites) ... and I came up with this SQL: ... Select @userId = tp_ID from UserInfo where tp_login like 'DOMAIN ...
    (microsoft.public.sharepoint.windowsservices)
  • WSS: Finding My Sites through SQL
    ... sites on a diferent server than the WSS server. ... foreach (XmlNode node in AllSites) ... and I came up with this SQL: ... Select @userId = tp_ID from UserInfo where tp_login like 'DOMAIN ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: login info from ie screen
    ... windows userid from the client machine, but it displays another userid that ... As if there is miscommunication between client and windows 2003 server. ... >> intranet userid that is different than her actual windows userid. ...
    (microsoft.public.frontpage.client)
  • Re: update multiple rows
    ... > for this you would need some sort of paging stored proc. ... > has to go to the server to send the info and to update the db, ... > 5) display page info to user. ... The end effort is to enable these 6 people to edit one ...
    (microsoft.public.inetserver.asp.general)