User SPs on a linked server

Question

Post reply

User SPs on a linked server

Krissy

Ten Centuries

Points: 1185
More actions
February 19, 2006 at 9:40 pm

#93644

After all my problems of setting up a linked server and permissions I have finally gotten rid of the permission problem.

Instead of setting up my linked server with 'SQL Server' selected as the type, I chose 'Other' and selected 'Microsoft OLE DB Provider for SQL Server'. I have made sure that RPC In and Out are enabled.

Now onto a NEW problem.

Connecting to ServerA, I can run something like

SELECT *
FROM [ServerB].[MyDB].[dbo].[SomeTable]

successfully.

But if I try to run a stored procedure, that I created

EXEC [ServerB].[MyDB].[dbo].[SomeProc]

then I will get an error:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'ServerB.dbo.SomeProc'.

BUT!!! if I try to execute a stored procedure that is a SYSTEM stored procedure it will work

For example:
EXEC [ServerB].[master].[dbo].[sp_who]

works fine!

Has anyone got any idea why I can execute SYSTEM stored procs but not USER ones?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

Did you grant permission to execute to public for procedure [ServerB].[MyDB].[dbo].[SomeProc]?

Krissy Ten Centuries Points: 1185 More actions · Answer 2

Yep

I have tried to call various SPs in different databases but get the same error regardless :/

Krissy Ten Centuries Points: 1185 More actions · Answer 3

OK after some further testing, it looks like when I call

EXEC [ServerB].[SomeDB].[dbo].[ProcName]

it doesn't try to run the procedure on ServerB, it looks for the procedure on itself (ie on ServerA!!!)

Any reason why it would ignore the [ServerB] part?!?!

Loner SSC-Insane Points: 21279 More actions · Answer 4

What kind of problem you had when you set up the linked server using SQL Server?

If you do sp_linkedservers, do you see the linked server you have set up, if you don't see it, that means the linked server did not set up properly.

Have you done sp_addlinkedsrvlogin to to ensure that all logins to the local server connect through to the linked server using their own user credentials?