Skip to content

Latest commit

 

History

History
105 lines (73 loc) · 4.72 KB

sp-addremotelogin-transact-sql.md

File metadata and controls

105 lines (73 loc) · 4.72 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_addremotelogin (Transact-SQL)
sp_addremotelogin adds a new remote login ID on the local server.
VanMSFT
vanto
randolphwest
01/23/2024
sql
system-objects
reference
sp_addremotelogin_TSQL
sp_addremotelogin
sp_addremotelogin
TSQL

sp_addremotelogin (Transact-SQL)

[!INCLUDE SQL Server]

Adds a new remote login ID on the local server. This enables remote servers to connect and execute remote procedure calls.

Important

[!INCLUDE ssNoteDepFutureAvoid] Use linked servers and linked server stored procedures instead.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_addremotelogin
    [ @remoteserver = ] N'remoteserver'
    [ , [ @loginame = ] N'loginame' ]
    [ , [ @remotename = ] N'remotename' ]
[ ; ]

Arguments

[ @remoteserver = ] N'remoteserver'

The name of the remote server that the remote login applies to. @remoteserver is sysname, with no default. If only @remoteserver is specified, all users on @remoteserver are mapped to existing logins of the same name on the local server. The server must be known to the local server. This is added by using sp_addserver. When users on @remoteserver connect to the local server that is running [!INCLUDE ssNoVersion] to execute a remote stored procedure, they connect as the local login that matches their own login on @remoteserver. @remoteserver is the server that initiates the remote procedure call.

[ @loginame = ] N'loginame'

The login ID of the user on the local instance of [!INCLUDE ssNoVersion]. @loginame is sysname, with a default of NULL. @loginame must already exist on the local instance of [!INCLUDE ssNoVersion]. If @loginame is specified, all users on @remoteserver are mapped to that specific local login. When users on @remoteserver connect to the local instance of [!INCLUDE ssNoVersion] to execute a remote stored procedure, they connect as @loginame.

[ @remotename = ] N'remotename'

The login ID of the user on the remote server. @remotename is sysname, with a default of NULL. @remotename must exist on @remoteserver. If @remotename is specified, the specific user @remotename is mapped to @loginame on the local server. When @remotename on @remoteserver connects to the local instance of [!INCLUDE ssNoVersion] to execute a remote stored procedure, it connects as @loginame. The login ID of @remotename can be different from the login ID on the remote server, @loginame.

Return code values

0 (success) or 1 (failure).

Remarks

To execute distributed queries, use sp_addlinkedsrvlogin.

sp_addremotelogin can't be used inside a user-defined transaction.

Permissions

Only members of the sysadmin and securityadmin fixed server roles can execute sp_addremotelogin.

Examples

A. Map one to one

The following example maps remote names to local names when the remote server ACCOUNTS and local server have the same user logins.

EXEC sp_addremotelogin 'ACCOUNTS';

B. Map many to one

The following example creates an entry that maps all users from the remote server ACCOUNTS to the local login ID Albert.

EXEC sp_addremotelogin 'ACCOUNTS', 'Albert';

C. Use explicit one-to-one mapping

The following example maps a remote login from the remote user Chris on the remote server ACCOUNTS to the local user salesmgr.

EXEC sp_addremotelogin 'ACCOUNTS', 'salesmgr', 'Chris';

Related content