sp_remoteoption System Stored Procedure

Displays or changes remote login options.

Syntax

sp_remoteoption [remoteserver, loginame, remotename, optname, {true | false}]

where

remoteserver: Specifies the remote server that has the remote login to which the option should apply.
loginame: Is the login name that identifies the remote login for the remoteserver, loginame, remotename combination.
remotename: Is the remote username that identifies the remote login for the remoteserver, loginame, remotename combination.
optname: Is the option you want to set or turn off. Currently, there is only one option, trusted, which means that the local server accepts remote logins from other servers without verifying user access for the particular remote login. The default is untrusted (trusted set to false), which results in password verification. SQL Server understands any unique string that is part of the option name. Use quotation marks around the option name if it includes embedded blanks.
true | false: Specifies whether to set the option. Choose true if you want to set the option, false if you want to turn it off.

Remarks

To display a list of the remote login options, execute sp_remoteoption with no parameters.

Only the system administrator can set or turn off particular server options. For details on remote login options, see the Microsoft SQL Server Administrator's Companion.

Examples

A. List Options

This example lists the remote login options:

sp_remoteoption
go
Settable remotelogin options.
remotelogin_option
--------------------------------------------
trusted

B. Trusted Logins

This example defines the remote login from the remote server ACCOUNTS to be trusted (the password will not be checked).

sp_remoteoption ACCOUNTS, salesmgr, chris, trusted, TRUE

C. Untrusted Logins

This example defines the remote login from the remote server ACCOUNTS to be untrusted (the password will be checked).

sp_remoteoption ACCOUNTS, salesmgr, chris, trusted, FALSE

Permission

Execute permission without parameters (display options only) defaults to the public group. Only the system administrator can use sp_remoteoption with parameters (to change an option).

Tables Used

master.dbo.sp_values, master.dbo.sysremotelogins, master.dbo.syssrvers