Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Execute stored procedure on SQL2008R2 from SQL2005 using linked servers

Posted on 2011-03-23
15
Medium Priority
?
274 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

604 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