The reason for the difference is that when XPs are run they are run under the context of the service accounts that are used for SQL Server. When you login to the server using query analyzer, your account probably has system admin or the needed rights on both servers to execute the stored procedure. When you run the XP, the rights that are being used are that of the service accounts for the SQL Server services. Check the rights of the service account and make sure it has appropriate rights on both the local server and the remote server. If use a domain account that has the necessary rights to run SQL Server you could use this same domain account for the SQL Server services on both of your servers.
For More Information
- Dozens more answers to tough SQL Server questions from Greg Robidoux and Jeremy Kadlec are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on SQL Server Stored Procedures
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.