Problem querying linked server

I have a problem querying a linked server (Oracle) from my SQL server 2000. I am able to query it normally but when I try to do it through a stored procedure i get the following message

Msg 7399, Sev 16: OLE DB provider 'MSDAORA' reported an error. Authentication failed. [SQLSTATE 42000]
Msg 7312, Sev 16: [SQLSTATE 01000]
Msg 7300, Sev 16: OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]. [SQLSTATE 01000]Can anybody help me ??

Originally posted by Enigma
I have a problem querying a linked server (Oracle) from my SQL server 2000. I am able to query it normally but when I try to do it through a stored procedure i get the following message

Msg 7399, Sev 16: OLE DB provider 'MSDAORA' reported an error. Authentication failed. [SQLSTATE 42000]
Msg 7312, Sev 16: [SQLSTATE 01000]
Msg 7300, Sev 16: OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]. [SQLSTATE 01000]|||Looks like you may have a problem with your access rights on either one of the servers......|||Well,

I am able to query the server when I am in the sql query analyzer.
I.E.
SELECT * INTO ABCD FROM TESTSVR..USER.ABCD

This works perfectly and i get the result

When I try to run this as a stored procedure

CREATE procedure sp_TransferData @.server varchar(100),@.userid varchar(30)
as
declare
@.tablename varchar (30),
@.fieldnametemp varchar(100),
@.query varchar (2500)
declare tablenames cursor for
select distinct table_name from TABLES

open tablenames
FETCH NEXT FROM tablenames into @.tablename
WHILE @.@.FETCH_STATUS = 0
begin
declare @.fieldname varchar(2000)
select @.fieldname = ''
declare fieldname cursor for
select field_name from tables where table_name = @.tablename
open fieldname
FETCH NEXT FROM fieldname into @.fieldnametemp
WHILE @.@.FETCH_STATUS = 0
begin
select @.fieldname = @.fieldnametemp + ',' + @.fieldname
fetch next from fieldname into @.fieldnametemp
end
CLOSE fieldname
DEALLOCATE fieldname
select @.fieldname = left(@.fieldname,len(@.fieldname)-1)
select @.query = 'select '+ @.fieldname + ' into ' + @.tablename + ' from ' + @.server + '..' + @.userid + '.' + @.tablename + ''')'
select @.query

execute (@.query)
print 'Table Processed'

fetch next from tablenames into @.tablename
end

CLOSE tablenames
DEALLOCATE tablenames

the error crops up ... can somebody suggest a way around