Execute stored procedure on SQL2008R2 from SQL2005 using linked servers

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.
LVL 3
adammet04Asked:
Who is Participating?
 
adammet04Connect With a Mentor Author Commented:
reboot of server fixed the issue.
0
 
CoolBurn28Commented:
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
 
JoeNuvoCommented:
Have you set RPC/RPC Out to true?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
CoolBurn28Commented:
The user(sql user)  has full access required on the database??
Does this mean you useSQL Login or  Domain\user (Windows Authentication)  ?
0
 
adammet04Author Commented:
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
 
kamindaCommented:
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
 
adammet04Author Commented:
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
 
adammet04Author Commented:
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
 
adammet04Author Commented:
woops - above should be 2008 R2 = SQL2008 R2
0
 
adammet04Author Commented:
JoeNuvo, RPC and RPCOut is enabled.
0
 
adammet04Author Commented:
JoeNuvo, RPC and RPCOut is enabled. but it did not work still..
0
 
kamindaCommented:
Try this on your SQL2008R2 to give execute permission to your user account

GRANT EXECUTE ON spname  TO user_or_role
0
 
adammet04Author Commented:
Kaminda,

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

0
 
adammet04Author Commented:
Kaminda,

I tried your suggestion with another user , and the same error has appeard but for the given user
0
 
adammet04Author Commented:
rebooting server ended up fixing this issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.