Solved

Execute stored procedure on SQL2008R2 from SQL2005 using linked servers

Posted on 2011-03-23
15
266 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
Comment Utility
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
Comment Utility
Have you set RPC/RPC Out to true?
0
 
LVL 2

Expert Comment

by:CoolBurn28
Comment Utility
The user(sql user)  has full access required on the database??
Does this mean you useSQL Login or  Domain\user (Windows Authentication)  ?
0
 
LVL 3

Author Comment

by:adammet04
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 3

Author Comment

by:adammet04
Comment Utility
woops - above should be 2008 R2 = SQL2008 R2
0
 
LVL 3

Author Comment

by:adammet04
Comment Utility
JoeNuvo, RPC and RPCOut is enabled.
0
 
LVL 3

Author Comment

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

Expert Comment

by:kaminda
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
reboot of server fixed the issue.
0
 
LVL 3

Author Closing Comment

by:adammet04
Comment Utility
rebooting server ended up fixing this issue.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now