Solved

Execute stored procedure on SQL2008R2 from SQL2005 using linked servers

Posted on 2011-03-23
15
268 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…

773 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