Security Issue While Selecting from a Linked Server:

[Abdul Gill]

NT 4/SP6a/ SQL7/SP1

I have a job owned by UserA on ServerA that selects data from ServerB. UserA
also exists on ServerB with same ID and password. UserA is dbo on ServerA
for all the databases. UserA is also member of the "System Administrators"
server role on ServerA. The job works OK. But if I remove it from "System
Administrators" server role and run the job, I get the following error
message and the job step fails:


Remote access not allowed for Windows NT user activated by SETUSER.
[SQLSTATE 42000] (Error 7410)


UserA on ServerB [remote server] has ReadOnly access. ServerB is linked to
ServerA using "they will be mapped to" option and UserA.

Any ideas!!!


Thanks

Abdul Gill

[Paul]

Hi, Abdul -

Try changing the owner of the job to 'sa' and reschedule.

I have had problems in the past when trying to run scheduled jobs as anyone but 'sa'. And with linked servers in the mix, any error is possible.

Hope this helps.
Paul


------------
Abdul Gill at 7/6/01 11:54:47 AM

NT 4/SP6a/ SQL7/SP1

I have a job owned by UserA on ServerA that selects data from ServerB. UserA
also exists on ServerB with same ID and password. UserA is dbo on ServerA
for all the databases. UserA is also member of the "System Administrators"
server role on ServerA. The job works OK. But if I remove it from "System
Administrators" server role and run the job, I get the following error
message and the job step fails:


Remote access not allowed for Windows NT user activated by SETUSER.
[SQLSTATE 42000] (Error 7410)


UserA on ServerB [remote server] has ReadOnly access. ServerB is linked to
ServerA using "they will be mapped to" option and UserA.

Any ideas!!!


Thanks

Abdul Gill