Re: sql agent permission

From: Eric (ericguo_00@yahoo.com)
Date: 06/28/02 

From: ericguo_00@yahoo.com (Eric)
Date: Fri, 28 Jun 2002 20:51:07 GMT

Sue,

Thanks for your response.

In terms of linked server configuration, all I said is that the sql
account used to connect the remote standby server is the same account
(with sysadmin rights) that owns the job. I got the following error
on step 3 in the job history. Note sqlservice is the local OS account
that mssqlserver service runs under.

Executed as user: DB1\sqlservice. Could not relay results of procedure
'gp_restore_db_backup' from remote server 'db2'. [SQLSTATE 42000]
(Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.

On Thu, 27 Jun 2002 15:46:04 -0600, Sue Hoegemeier
<Sue_H@nomail.please> wrote:

>And some of what you are asking may depend on the security
>mapping of the linked server but I really lost what you were
>saying in terms of your linked server configuration.
>
>-Sue
>
>On Thu, 27 Jun 2002 21:26:02 GMT, eric.guo@gtnexus.com
>(Eric) wrote:
>
>>Hi,
>>
>>I have a situation and hope someone can give me a pointer.
>>
>>I have two w2k servers (serverA and serverB) that are not in a domain
>>and I want to set up serverB as a standby server. I run a sql agent
>>job on serverA to perform the followings in sequence.
>>
>>1. back up the production database on serverA
>>2. copy the backup file from serverA to serverB
>>3. call a stored procedure on serverB to restore the database on
>>serverB.
>>
>>While step 1 and 2 work fine, I just can not have the remote sp to
>>restore the database.
>>
>>The job on serverA is owned by a sql account (ABC) that is in
>>sysadmin role.
>>
>>The sqlserveragent and msssqlserver services are run under a local
>>account(XYZ) that is in local admin group.
>>
>>The linked server is created with the connection from serverA to
>>serverB as ABC. ABC has exec right on the stored procedure.
>>
>>The job history shows that "the job is executed as serverA\XYZ". My
>>question is when step 3 gets executed, is it ABC that connects the
>>serverB and runs the sp or is it serverA\XYZ, which should fail?
>>
>>TIA
>>
>>
>>Eric
>

Relevant Pages

  • RE: SQL Mail
    ... For SQL Mail to work, you need to have the service running under a domain ... Unless it is a domain account, ... > need to be able to send email via SQL from ServerB to ServerA. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Mail
    ... local account syncd with same password as domain account.. ... I have another mailbox on the domain XXX called pete.. ... > need to be able to send email via SQL from ServerB to ServerA. ...
    (microsoft.public.sqlserver.server)
  • RE: Copy Database Wizard Fails to Copy Files
    ... I had the exact same problem until I executed the package under an account ... Regards, ... > simple table) on ServerA to ServerB. ... > Put the database Test in single user mode.......Ok ...
    (microsoft.public.sqlserver.dts)
  • Re: who is Machinename$
    ... That is the account name of the computer. ... > We have a windows 2003 web server named ServerA which has an ODBC ... > ServerB, ... > Anyone knows who is Machinename$, and any reference would be great! ...
    (microsoft.public.windows.server.general)
  • Re: Getting Msg 18456 in referencing a database from a linked server
    ... to authenticate to serverb. ... You should try adding your linked server passing an SQL ... authentication/Valid Windows account depending on your security type. ...
    (microsoft.public.sqlserver.server)