sql agent permission

From: Eric (eric.guo@gtnexus.com)
Date: 06/27/02 

From: eric.guo@gtnexus.com (Eric)
Date: Thu, 27 Jun 2002 21:26:02 GMT

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 agent permission
    ... Job ownership affects the security context of jobs in the ... If the owner of a job is a member of the sysadmin role then ... >and I want to set up serverB as a standby server. ... >serverB as ABC. ...
    (microsoft.public.sqlserver.security)
  • Re: sql agent permission
    ... saying in terms of your linked server configuration. ... >and I want to set up serverB as a standby server. ... call a stored procedure on serverB to restore the database on ... >serverB as ABC. ...
    (microsoft.public.sqlserver.security)
  • Re: Restore Database
    ... free space and then create additional filegroups/files to distribute the ... Then you can backup this database and restore it to serverB. ...
    (microsoft.public.sqlserver.server)
  • Restore Database
    ... I have one database backup file backup.bak having database file abc.mdf 60GB ... I wish to restore the backup into other serverB, ... 50GB) free space. ...
    (microsoft.public.sqlserver.server)
  • Re: Database Diagram
    ... If I remember well a diagram is stored in dtproperties system table. ... > I created a database with diagram in ServerA. ... > restore this database in ServerB. ...
    (microsoft.public.sqlserver.server)