sp_remoteoption (T-SQL)

Displays or changes options for a remote login defined on the local server running Microsoft® SQL Server™.

Syntax

sp_remoteoption [[@remoteserver =] 'remoteserver']
    [,[@loginame =] 'loginame']
    [,[@remotename =] 'remotename']
    [,[@optname =] 'optname']
    [,[@optvalue =] 'optvalue']

Arguments

[@remoteserver =] 'remoteserver'

Is the name of the remote server that the remote login applies to. remoteserver is sysname, with a default of NULL. The server must be known to the local server (added using sp_addserver). remoteserver is the server that initiates remote procedure calls to the local server.

[@loginame =] 'loginame'

Is the login ID of the user on the local SQL Server. login is sysname, with a default of NULL. login must already exist on the local SQL Server.

[@remotename =] 'remotename'

Is the login ID of the user on remoteserver. remote_name is sysname, with a default of NULL. remotename must exist on remoteserver.

[@optname =] 'optname'

Is the option to set or turn off. optname is varchar(35), with a default of NULL. trusted is the only option. When the option is set, the local server accepts remote logins from remote servers without verifying user access for the particular remote login. The default server behavior is untrusted (trusted set to FALSE), resulting in password verification of the remote login when connecting to the local SQL Server from the remote server.

[@optvalue =] 'optvalue'

Is the value for optname. optvalue is varchar(10), with a default of NULL. Set to TRUE to set optname, FALSE to turn it off.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Remarks

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

sp_remoteoption cannot be executed within a user-defined transaction.

Permissions

Only members of the sysadmin or securityadmin fixed server roles can execute sp_remoteoption.

Examples

A. List options

This example lists the remote login options.

EXEC sp_remoteoption

go

Settable remotelogin options.

remotelogin_option

--------------------------------------------

trusted

B. Accept trusted logins

This example defines a remote login chris, mapped to the local login salesmgr, from the remote server ACCOUNTS to be trusted (the password is not checked).

EXEC sp_remoteoption 'ACCOUNTS', 'salesmgr', 'chris', 'trusted', 'TRUE'

C. Verify untrusted logins

This example defines a remote login chris, mapped to the local login salesmgr, from the remote server ACCOUNTS to be untrusted (the password is checked).

EXEC sp_remoteoption 'ACCOUNTS', 'salesmgr', 'chris', 'trusted', 'FALSE'

See Also