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.
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.
Have you set RPC/RPC Out to true?
The user(sql user) has full access required on the database??
Does this mean you useSQL Login or Domain\user (Windows Authentication) ?
Does this mean you useSQL Login or Domain\user (Windows Authentication) ?
ASKER
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?
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?
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.
ASKER
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.
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.
ASKER
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.
could there be a setting in 2008 R2 which limits running of SPROCS from another server.
ASKER
woops - above should be 2008 R2 = SQL2008 R2
ASKER
JoeNuvo, RPC and RPCOut is enabled.
ASKER
JoeNuvo, RPC and RPCOut is enabled. but it did not work still..
Try this on your SQL2008R2 to give execute permission to your user account
GRANT EXECUTE ON spname TO user_or_role
GRANT EXECUTE ON spname TO user_or_role
ASKER
Kaminda,
That will not work because the user i am connecting with is the owner of the database (full access already).
That will not work because the user i am connecting with is the owner of the database (full access already).
ASKER
Kaminda,
I tried your suggestion with another user , and the same error has appeard but for the given user
I tried your suggestion with another user , and the same error has appeard but for the given user
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
rebooting server ended up fixing this issue.
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