Msg 18483 when trying to execute remote procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Msg 18483 when trying to execute remote procedure

Author

Topic

vicferrada
Starting Member

11 Posts

Posted - 2007-12-18 : 12:36:30

using SQL Server 2005 sp2

I've added the linked server options with the appropriate login credentials (Be made using this security context) to access the remote server. The account used is db_owner on both the desired db and the master db.

When I query [server\instance].database.dbo.customers I get the results I desire but when I try to execute a stored procedure on that database, exec [server\instance].database.dbo.procCustomers, the following error occurs:

Msg 18483, Level 14, State 1, Line 1
Could not connect to server 'server\instance' because 'logn' is not defined as a remote login at the server. Verify that you have specified the correct login name. .

Thanks for any help.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-12-18 : 14:36:55

Are you sure that the 'login' is a remote login?
You can do this by running the following (replacing with the actual values) from the remote server.
sp_AddRemoteLogin 'linkedserver1','loginname'

Future guru in the making.

vicferrada
Starting Member

11 Posts

Posted - 2007-12-18 : 16:09:44

Like I said,
select * from [server\instance].database.dbo.customers
works fine. Inserting to that table works fine (credentials through db_owner login).

exec [server\instance].database.dbo.procCustomers
doesn't.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-18 : 21:34:46

Tried this?

exec (dbo.procCustomers) at [server\instance]

montu
Yak Posting Veteran

60 Posts

Posted - 2007-12-18 : 23:10:31

i think u need some other rights(sysadmin..........) as well to execute jobs db_owner may not have Privilege to execute the job

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 00:30:11

Didn't mention job at all in OP.

vicferrada
Starting Member

11 Posts

Posted - 2007-12-19 : 10:23:33

rmiao, that exec() still gives the connection error :(

montu, this isn't a job, simply trying to run a query. The procedure does exactly the same thing as the the query specified above, the query works, the call to the procedure doesn't.

vicferrada
Starting Member

11 Posts

Posted - 2007-12-19 : 10:25:08

sorry, just to add...

select * from [server\instance].database.dbo.customers
this works fine

exec [server\instance].database.dbo.procCustomers
this doesn't

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 10:32:37

Do the remote stored procedure need PARAMETERS?
exec [server\instance].database.dbo.procCustomers 1, '20070101'

Do you have EXECUTE permisson on the remote SP?

E 12°55'05.25"
N 56°04'39.16"

pootle_flump

1064 Posts

Posted - 2007-12-19 : 11:34:38

Have you enabled remote procedure calls on the servers?

vicferrada
Starting Member

11 Posts

Posted - 2007-12-19 : 13:41:38

RPC enabled and just as a trial run, I gave sysadmin privilege to the login. Same error :-/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 15:02:48

How did you map user to linked server? Does that remote sql login have proper permission?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 15:19:32

What about parameters?

Like this sp

exec sp_columns

it fails when not passing any parameters. but when you do this

exec sp_columns test

the call is ok.

E 12°55'05.25"
N 56°04'39.16"

vicferrada
Starting Member

11 Posts

Posted - 2007-12-19 : 15:46:18

If it were a procedure call missing parameters it wouldn't give you the above message and besides, that procedure needs no parameters, it's a simple select * on a small table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources