23/05/13 sp_addlinkedsrvlogin (Transact-SQL)

sp_addlinkedsrvlogin
(Transact-SQL)
SQL Server 2012 8 out of 12 rated this helpful

Creates or updates a mapping between a login on the local

instance of SQL Server and a security account on a remote
server.

Transact-SQL Syntax Conventions

Syntax

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'

[ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]

Arguments
[ @rmtsrvname = ] 'rmtsrvname'
Is the name of a linked server that the login mapping
applies to. rmtsrvname is sysname, with no default.

[ @useself = ] 'TRUE' | 'FALSE' | 'NULL'

Determines whether to connect to rmtsrvname by
impersonating local logins or explicitly submitting a
login and password. The data type is varchar(8), with
a default of TRUE.

A value of TRUE specifies that logins use their own

credentials to connect to rmtsrvname, with the rmtuser
and rmtpassword arguments being ignored. FALSE
specifies that the rmtuser and rmtpassword arguments
are used to connect to rmtsrvname for the specified
locallogin. If rmtuser and rmtpassword are also set to
NULL, no login or password is used to connect to the
linked server.

[ @locallogin = ] 'locallogin'
msdn.microsoft.com/en-us/librarY/ms189811.aspx Is a login on the local server. locallogin is sysname, 1/5
23/05/13 sp_addlinkedsrvlogin (Transact-SQL)
Is a login on the local server. locallogin is sysname,
with a default of NULL. NULL specifies that this entry
applies to all local logins that connect to rmtsrvname.
If not NULL, locallogin can be a SQL Server login or a
Windows login. The Windows login must have been
granted access to SQL Server either directly, or
through its membership in a Windows group granted
access.

[ @rmtuser = ] 'rmtuser'
Is the remote login used to connect to rmtsrvname
when @useself is FALSE. When the remote server is
an instance of SQL Server that does not use Windows
Authentication, rmtuser is a SQL Server login. rmtuser
is sysname, with a default of NULL.

[ @rmtpassword = ] 'rmtpassword'
Is the password associated with rmtuser. rmtpassword
is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Remarks
When a user logs on to the local server and executes a
distributed query that accesses a table on the linked server,
the local server must log on to the linked server on behalf of
the user to access that table. Use sp_addlinkedsrvlogin to
specify the login credentials that the local server uses to log
on to the linked server.

Note

To create the best query plans when you are using a

table on a linked server, the query processor must have
data distribution statistics from the linked server. Users
that have limited permissions on any columns of the table
might not have sufficient permissions to obtain all the
useful statistics, and might receive a less efficient query
plan and experience poor performance. If the linked
server is an instance of SQL Server, to obtain all available
statistics, the user must own the table or be a member of
the sysadmin fixed server role, the db_owner fixed
database role, or the db_ddladmin fixed database role
msdn.microsoft.com/en-us/librarY/ms189811.aspx on the linked server. SQL Server 2012 SP1 modifies the 2/5
23/05/13 sp_addlinkedsrvlogin (Transact-SQL)

on the linked server. SQL Server 2012 SP1 modifies the

permission restrictions for obtaining statistics and allows
users with SELECT permission to access statistics
available through DBCC SHOW_STATISTICS. For more
information, see the Permissions section of DBCC
SHOW_STATISTICS (Transact-SQL).

A default mapping between all logins on the local server

and remote logins on the linked server is automatically
created by executing sp_addlinkedserver. The default
mapping states that SQL Server uses the user credentials of
the local login when connecting to the linked server on
behalf of the login. This is equivalent to executing
sp_addlinkedsrvlogin with @useself set to true for the linked
server, without specifying a local user name. Use
sp_addlinkedsrvlogin only to change the default mapping or
to add new mappings for specific local logins. To delete the
default mapping or any other mapping, use
sp_droplinkedsrvlogin.

Instead of having to use sp_addlinkedsrvlogin to create a

predetermined login mapping, SQL Server can automatically
use the Windows security credentials (Windows login name
and password) of a user issuing the query to connect to a
linked server when all the following conditions exist:

A user is connected to SQL Server by using Windows

Authentication Mode.

Security account delegation is available on the client

and sending server.

The provider supports Windows Authentication

Mode; for example, SQL Server running on Windows.

Note

Delegation does not have to be enabled for single-hop

scenarios, but it is required for multiple-hop scenarios.

After the authentication has been performed by the linked

server by using the mappings that are defined by executing
sp_addlinkedsrvlogin on the local instance of SQL Server, the
permissions on individual objects in the remote database
are determined by the linked server, not the local server.

sp_addlinkedsrvlogin cannot be executed from within a user-

defined transaction.

msdn.microsoft.com/en-us/librarY/ms189811.aspx Permissions 3/5

23/05/13 sp_addlinkedsrvlogin (Transact-SQL)

Permissions
Requires ALTER ANY LOGIN permission on the server.

Examples

A. Connecting all local logins to the linked server

by using their own user credentials
The following example creates a mapping to make sure that
all logins to the local server connect through to the linked
server Accountsby using their own user credentials.

EXEC sp_addlinkedsrvlogin 'Accounts';

Or

EXEC sp_addlinkedsrvlogin 'Accounts', 'true';

Note

If there are explicit mappings created for individual

logins, they take precedence over any global mappings
that may exist for that linked server.

B. Connecting a specific login to the linked

server by using different user credentials
The following example creates a mapping to make sure that
the Windows user Domain\Maryconnects through to the
linked server Accountsby using the login MaryPand
password d89q3w4u.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain

Security Note

This example does not use Windows Authentication.

Passwords will be transmitted unencrypted. Passwords
msdn.microsoft.com/en-us/librarY/ms189811.aspx 4/5
23/05/13 sp_addlinkedsrvlogin (Transact-SQL)
Passwords will be transmitted unencrypted. Passwords
may be visible in data source definitions and scripts that
are saved to disk, in backups, and in log files. Never use
an administrator password in this kind of connection.
Consult your network administrator for security guidance
specific to your environment.

See Also
Reference
Linked Servers Catalog Views (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_droplinkedsrvlogin (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

© 2013 Microsoft. All rights reserved.

msdn.microsoft.com/en-us/librarY/ms189811.aspx 5/5