?
Solved

Execute stored procedure on SQL2008R2 from SQL2005 using linked servers

Posted on 2011-03-23
15
Medium Priority
?
272 Views
Last Modified: 2012-05-11
All,

I am having some trouble executing a stored procedure via a linked server from SQL2005 to SQL2008R2. I can select data fine via the linked server, but executing a stored procedure is not working, i get this error when i try to execute it

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'user1'

The user(sql user)  has full access required on the database in question,
and the linked server is set so that the local login is impersonating the remote user

I also tried not impersonating and manually entering the details.
0
Comment
Question by:adammet04
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 2
  • 2
  • +1
15 Comments
 
LVL 2

Expert Comment

by:CoolBurn28
ID: 35203917
may be this site can help you.
might be the link set-up
Check this site for missing procedures

http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35203921
Have you set RPC/RPC Out to true?
0
 
LVL 2

Expert Comment

by:CoolBurn28
ID: 35203934
The user(sql user)  has full access required on the database??
Does this mean you useSQL Login or  Domain\user (Windows Authentication)  ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Author Comment

by:adammet04
ID: 35203973
CoolBurn28,

I have already reviewed that site, but i am using SQL Authentication, not windows authentication, so i dont think kerberos comes into it here.

JoeNuvo,

do we set RPC/RPC out on the linked server, or on the local server?
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35204370
I think you should double check your users access rights to the SP and its underline tables. As you can run other statements on this linked server it must be an issue with access right. Try granting permmission for required objects and check.
0
 
LVL 3

Author Comment

by:adammet04
ID: 35204507
Kaminda,

the user i am connecting with is the owner of the database and also a sysadmin on the box (not my choice!), selects to tables is ok, just not executing of stored procedures.
0
 
LVL 3

Author Comment

by:adammet04
ID: 35204522
Setting up a linked server to a different sql 2005 box allows me to run stored procedures.

could there be a setting in 2008 R2 which limits running of SPROCS from another server.
0
 
LVL 3

Author Comment

by:adammet04
ID: 35204525
woops - above should be 2008 R2 = SQL2008 R2
0
 
LVL 3

Author Comment

by:adammet04
ID: 35204547
JoeNuvo, RPC and RPCOut is enabled.
0
 
LVL 3

Author Comment

by:adammet04
ID: 35204551
JoeNuvo, RPC and RPCOut is enabled. but it did not work still..
0
 
LVL 9

Expert Comment

by:kaminda
ID: 35204557
Try this on your SQL2008R2 to give execute permission to your user account

GRANT EXECUTE ON spname  TO user_or_role
0
 
LVL 3

Author Comment

by:adammet04
ID: 35204584
Kaminda,

That will not work because the user i am connecting with is the owner of the database (full access already).

0
 
LVL 3

Author Comment

by:adammet04
ID: 35204597
Kaminda,

I tried your suggestion with another user , and the same error has appeard but for the given user
0
 
LVL 3

Accepted Solution

by:
adammet04 earned 0 total points
ID: 36907570
reboot of server fixed the issue.
0
 
LVL 3

Author Closing Comment

by:adammet04
ID: 36935339
rebooting server ended up fixing this issue.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question