April 5, 2007 at 2:52 pm
Hi all,
I'm trying to write a stored procedure that temporarily maps a specific login to a remote user name and password on a linked server, executes a query, then removes the remote server mapping. I've googled like crazy but can't seem to find anything.
Here's the code:
exec
sp_addlinkedsrvlogin
@rmtsrvname
= 'xxxx',
@useself
= 'False',
@locallogin
= 'CBTest',
@rmtuser
= 'xxxx',
@rmtpassword
= 'xxxxx'
SELECT
...
exec sp_droplinkedsrvlogin
@rmtsrvname
= 'xxxx',
@locallogin
= 'CBTest'
Here's the error:
Server: Msg 18456, Level 14, State 1, Line 0
Login failed for user 'CBTest'.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "xxx" returned message "Communication link failure".
When I run it as myself, it works fine, but when I log in as the user, I get that error message. Any suggestions?
Thanks! -Josh
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
April 5, 2007 at 9:56 pm
Does the user exist on the remote server?
April 6, 2007 at 6:08 am
Meaning does the local login I'm attempting to map exist on the remote server? Nope. That's why I'm trying to map it to a different login. See, I want this local user to be able to grab data from a linked server, but only in the context of this stored procedure, so I'm trying to encapsulate the logic of adding and dropping the linked server login within the procedure. Make sense?
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply