DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 811031 - Last Review: May 10, 2007 - Revision: 4.5

On This Page

SYMPTOMS

When you run a SQL Server Agent job that uses a linked server and the owner of the job is not a system administrator account, the job may fail and SQL Server displays the following error message:
Remote access not allowed for Windows NT user activated by SETUSER. [SQLSTATE 42000] (Error 7410). The step failed.

CAUSE

When you run a SQL Server Agent job that is not owned by a system administrator account, SQL Server Agent runs SETUSER to impersonate the owner of the job.

If the job uses linked servers, the job may fail because remote access is not allowed after the execution of SETUSER.

WORKAROUND

To work around this problem, use one of the following methods:

Method 1

Make the system administrator the owner of the job.

Method 2

Use mapped security context for the linked server and modify the job to run as OSQL.

To set the mapped security context for the linked server:
  1. Right-click the linked server, and then click Properties.
  2. Click the Security tab.
  3. Select either of the following options.
    • Be made using the login's current security context
    • Be made using this security context
To modify the job to run as OSQL:
  1. Right-click the job, and then click Properties.
  2. In the Steps tab, click the Step Name that you want to edit, and then click Edit.
  3. On the General tab of the Edit Job Step dialog box, click Operating System Command (CmdExec) in the Type list.
  4. In the Command text box, type osql –E –Q “Exec storeProc”.
  5. In the Edit Job Step dialog box, click OK.
  6. In the Properties dialog box, click OK.

STATUS

This behavior is by design.

MORE INFORMATION

For more information, visit the following Microsoft Web site:
How to Reset SQLAgentCmdExec Permissions (Enterprise Manager)
http://msdn2.microsoft.com/en-us/library/aa176987(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa176987(SQL.80).aspx)

APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kberrmsg kbprb KB811031
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support