sp_addlinkedsrvlogin (Transact-SQL)sp_addlinkedsrvlogin (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

创建或更新 SQL ServerSQL Server 本地实例上的登录名与远程服务器中安全帐户之间的映射。Creates or updates a mapping between a login on the local instance of SQL ServerSQL Server and a security account on a remote server.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon 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. rmtsrvnamesysname,无默认值。rmtsrvname is sysname, with no default.

[ @useself = ] { 'TRUE' | 'FALSE' | NULL }'
确定是否连接到rmtsrvname通过模拟本地登录名或显式提交登录名和密码。Determines whether to connect to rmtsrvname by impersonating local logins or explicitly submitting a login and password. 数据类型是varchar ( 8 ) ,默认值为 TRUE。The data type is varchar( 8 ), with a default of TRUE.

值为 TRUE 指定登录名使用自己的凭据来连接到rmtsrvname,使用rmtuserrmtpassword参数被忽略。A value of TRUE specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. FALSE 指定rmtuserrmtpassword参数用于连接到rmtsrvname指定locallogin.FALSE specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. 如果rmtuserrmtpassword也是设置为 NULL、 没有登录名或密码用于连接到链接服务器。If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.

[ @locallogin = ] 'locallogin'
本地服务器上的登录。Is a login on the local server. localloginsysname,默认值为 NULL。locallogin is sysname, with a default of NULL. NULL 指定此项应用于连接到的所有本地登录rmtsrvnameNULL specifies that this entry applies to all local logins that connect to rmtsrvname. 如果不为 NULL, locallogin可以是SQL ServerSQL Server登录名或 Windows 登录名。If not NULL, locallogin can be a SQL ServerSQL Server login or a Windows login. 对于 Windows 登录来说,必须以直接的方式或通过已被授权访问的 Windows 组成员身份授予其访问 SQL ServerSQL Server 的权限。The Windows login must have been granted access to SQL ServerSQL Server either directly, or through its membership in a Windows group granted access.

[ @rmtuser = ] 'rmtuser'
用于连接到的远程登录名rmtsrvname时@useself为 FALSE。Is the remote login used to connect to rmtsrvname when @useself is FALSE. 远程服务器时的实例SQL ServerSQL Server不使用 Windows 身份验证rmtuserSQL ServerSQL Server登录名。When the remote server is an instance of SQL ServerSQL Server that does not use Windows Authentication, rmtuser is a SQL ServerSQL Server login. rmtusersysname,默认值为 NULL。rmtuser is sysname, with a default of NULL.

[ @rmtpassword = ] 'rmtpassword'
使用关联的密码rmtuserIs the password associated with rmtuser. rmtpasswordsysname,默认值为 NULL。rmtpassword is sysname, with a default of NULL.

返回代码值Return Code Values

0(成功)或 1(失败)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. 使用 sp_addlinkedsrvlogin 来指定本地服务器用于登录链接服务器的登录凭据。Use sp_addlinkedsrvlogin to specify the login credentials that the local server uses to log on to the linked server.

备注

若要在某一链接服务器上使用表时创建最佳查询计划,查询处理器必须具有来自该链接服务器的数据分布统计。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. 如果链接服务器是 SQL ServerSQL Server 的实例,若要获取所有可用的统计,用户必须拥有该表或者是链接服务器上 sysadmin 固定服务器角色、db_owner 固定数据库角色或者 db_ddladmin 固定数据库角色的成员。If the linked server is an instance of SQL ServerSQL 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 on the linked server. SQL Server 2012 SP1 修改了这些权限限制以获取统计信息,允许具有 SELECT 权限的用户访问通过 DBCC SHOW_STATISTICS 提供的统计信息。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. 有关详细信息,请参阅的权限部分DBCC SHOW_STATISTICS (TRANSACT-SQL)For more information, see the Permissions section of DBCC SHOW_STATISTICS (Transact-SQL).

本地服务器上的所有登录和链接服务器上的远程登录之间的默认映射通过执行 sp_addlinkedserver 自动创建。A default mapping between all logins on the local server and remote logins on the linked server is automatically created by executing sp_addlinkedserver. 默认映射表示,当代表本地登录连接到链接服务器时,SQL ServerSQL Server 使用本地登录的用户凭据。The default mapping states that SQL ServerSQL Server uses the user credentials of the local login when connecting to the linked server on behalf of the login. 这相当于执行使用 sp_addlinkedsrvlogin@useself设置为 true链接服务器,无需指定本地用户名。This is equivalent to executing sp_addlinkedsrvlogin with @useself set to true for the linked server, without specifying a local user name. 使用 sp_addlinkedsrvlogin 只可以更改特定的本地服务器的默认映射或添加新映射。Use sp_addlinkedsrvlogin only to change the default mapping or to add new mappings for specific local logins. 若要删除默认映射或任何其他映射,请使用 sp_droplinkedsrvlogin。To delete the default mapping or any other mapping, use sp_droplinkedsrvlogin.

当所有下列条件都存在时,SQL ServerSQL Server 可以自动地使用正在发出查询的用户的 Windows 安全凭据(Windows 登录名和密码),以连接到链接服务器,而不必使用 sp_addlinkedsrvlogin 来创建一个预设的登录映射:Instead of having to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL ServerSQL 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:

  • 使用 Windows 身份验证模式,用户连接到 SQL ServerSQL ServerA user is connected to SQL ServerSQL Server by using Windows Authentication Mode.

  • 在客户端和发送服务器上安全帐户委托是可用的。Security account delegation is available on the client and sending server.

  • 提供程序支持 Windows 身份验证模式(例如,运行于 Windows 上的 SQL ServerSQL Server)。The provider supports Windows Authentication Mode; for example, SQL ServerSQL Server running on Windows.

备注

对于单跃点方案,不必启用委托,但对于多跃点方案,则需要启用委托。Delegation does not have to be enabled for single-hop scenarios, but it is required for multiple-hop scenarios.

由链接服务器使用映射(此映射通过在本地 SQL ServerSQL Server 实例上执行 sp_addlinkedsrvlogin 而定义)执行身份验证后,对于远程数据库中单个对象的权限将由链接服务器决定,而不是由本地服务器决定。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 ServerSQL Server, the permissions on individual objects in the remote database are determined by the linked server, not the local server.

不能从用户定义的事务中执行 sp_addlinkedsrvlogin。sp_addlinkedsrvlogin cannot be executed from within a user-defined transaction.

权限Permissions

要求对服务器拥有 ALTER ANY LOGIN 权限。Requires ALTER ANY LOGIN permission on the server.

示例Examples

A.A. 使用各自的用户凭据将所有本地登录连接到链接服务器Connecting all local logins to the linked server by using their own user credentials

以下示例将创建一个映射,以确保所有到本地服务器的登录都使用其各自的用户凭据连接到链接服务器 AccountsThe following example creates a mapping to make sure that all logins to the local server connect through to the linked server Accounts by using their own user credentials.

EXEC sp_addlinkedsrvlogin 'Accounts';  

Or

EXEC sp_addlinkedsrvlogin 'Accounts', 'true';  

备注

如果为单个登录创建了显式映射,则其优先于此链接服务器的任何全局映射。If there are explicit mappings created for individual logins, they take precedence over any global mappings that may exist for that linked server.

B.B. 使用不同的用户凭据将特定的登录连接到链接服务器Connecting a specific login to the linked server by using different user credentials

以下示例将创建一个映射,以确保 Windows 用户 Domain\Mary 使用登录名 Accounts 和密码 MaryP 连接到链接服务器 d89q3w4uThe following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server Accounts by using the login MaryP and password d89q3w4u.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u';  

重要

此示例不使用 Windows 身份验证。This example does not use Windows Authentication. 密码未经加密而进行传输。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

链接的服务器目录视图(Transact SQL) Linked Servers Catalog Views (Transact-SQL)
sp_addlinkedserver (Transact-SQL) sp_addlinkedserver (Transact-SQL)
sp_droplinkedsrvlogin (TRANSACT-SQL) sp_droplinkedsrvlogin (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)