
	Home > Ask the SQL Server Experts > Greg Robidoux - Backup and Recovery Questions & Answers > Error running a stored procedure that references a remote server

Ask The SQL Server Expert: Questions & Answers

EMAIL THIS

Error running a stored procedure that references a remote server

EXPERT RESPONSE FROM: Greg Robidoux

		Pose a Question

		Other SQL Server Categories

		Meet all SQL Server Experts
		Become an Expert for this site

Digg This!

StumbleUpon

Del.icio.us

QUESTION POSED ON: 04 December 2004
I am running a stored procedure that has some references to a remote server through linked server. The stored procedure gets run OK when I run it through query analyzer. However, when I attempted to to call it using xp_Sendmail @query 'Exec StoreProc_name', I am getting this error: "ODBC error 7410 (42000) Remote access not allowed for Windows NT user activated by SETUSER." I am running Window 2003/SQL Server 2003 on cluster congfiguration. Any help you can give me would be greatly appreciated. I looked at Microsoft's Web site, http://support.microsoft.com/default.aspx?scid=kb;en-us;811031&Product=sql2k. I performed their recommendation, but I am still getting the error.

EXPERT RESPONSE

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.

Digg This!

StumbleUpon

Del.icio.us

RELATED CONTENT

	Greg Robidoux - Backup and Recovery
	Synchronize databases on two separate active servers
	Back up tables from SQL Server 2005 database to a file
	Restore SQL Server 2000 backups in SQL Server 2005
	Restoring a SQL Server database with a corrupt data file
	SQL Server error message: "Server out of memory"
	Configure log shipping between SQL Server 2000 and 2005
	Restoring from msdb with only .mdf and .ldf files
	Extended stored procedure to back up databases
	Change tempdb from 'C' drive
	Back up stored procedure only

SQL Server stored procedures

SQL Server trigger vs. stored procedure to receive data notification

How to use SQL Server 2008 hierarchyid data type

SQL and SQL Server Tutorial and Reference Guide

SQL Server stored procedures tutorial: Write, tune and get examples

Check SQL Server database and log file size with this stored procedure

SQL Server source code analysis and management adds database security

Configure SQL Server Service Broker for sending stored procedure data

Find size of SQL Server tables and other objects with stored procedure

Track changes to SQL Server 2000 and 2005 with one simple utility

Troubleshoot SQL Server 2005 temporary table performance problems

RELATED GLOSSARY TERMS

Terms from Whatis.com − the technology online dictionary

library (SearchSQLServer.com)

trigger (SearchSQLServer.com)

RELATED RESOURCES

2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems

Search Bitpipe.com for the latest white papers and business webcasts

Whatis.com, the online computer dictionary

Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.

Browse our Expert Advice
Operating Systems IT Management Networking Database	Security Servers and Storage Applications and Development Career Center

SEARCH

TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.


TechTarget Corporate Web Site \| Media Kits \| Reprints \| Site Map All Rights Reserved, Copyright 2005 - 2008, TechTarget \| Read our Privacy Policy