sp_helplinkedsrvlogin (Transact-SQL)
TOC
Collapse the table of content
Expand the table of content

sp_helplinkedsrvlogin (Transact-SQL)

 

Provides information about login mappings defined against a specific linked server used for distributed queries and remote stored procedures.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
sp_helplinkedsrvlogin [ [ @rmtsrvname = ] 'rmtsrvname' ]   
     [ , [ @locallogin = ] 'locallogin' ]  

[ @rmtsrvname=] 'rmtsrvname'
Is the name of the linked server that the login mapping applies to. rmtsrvname is sysname, with a default of NULL. If NULL, all login mappings defined against all the linked servers defined in the local computer running SQL Server are returned.

[ @locallogin=] 'locallogin'
Is the SQL Server login on the local server that has a mapping to the linked server rmtsrvname. locallogin is sysname, with a default of NULL. NULL specifies that all login mappings defined on rmtsrvname are returned. If not NULL, a mapping for locallogin to rmtsrvname must already exist. locallogin can be a SQL Server login or a Windows user. The Windows user must have been granted access to SQL Server either directly or through its membership in a Windows group that has been granted access.

0 (success) or 1 (failure)

Column nameData typeDescription
Linked ServersysnameLinked server name.
Local LoginsysnameLocal login for which the mapping applies.
Is Self Mappingsmallint0 = Local Login is mapped to Remote Login when connecting to Linked Server.

1 = Local Login is mapped to the same login and password when connecting to Linked Server.
Remote LoginsysnameLogin name on LinkedServer that is mapped to LocalLogin when IsSelfMapping is 0. If IsSelfMapping is 1, RemoteLogin is NULL.

Before you delete login mappings, use sp_helplinkedsrvlogin to determine the linked servers that are involved.

No permissions are checked.

A. Displaying all login mappings for all linked servers

The following example displays all login mappings for all linked servers defined on the local computer running SQL Server.

EXEC sp_helplinkedsrvlogin;  
GO  

Here is the result set.

Linked Server    Local Login   Is Self Mapping Remote Login   
---------------- ------------- --------------- --------------   
Accounts         NULL          1               NULL  
Sales            NULL          1               NULL  
Sales            Mary          0               sa  
Marketing        NULL          1               NULL  
  
(4 row(s) affected)  

B. Displaying all login mappings for a linked server

The following example displays all locally defined login mappings for the Sales linked server.

EXEC sp_helplinkedsrvlogin 'Sales';  
GO  

Here is the result set.

Linked Server    Local Login   Is Self Mapping Remote Login   
---------------- ------------- --------------- --------------   
Sales            NULL          1               NULL  
Sales            Mary          0               sa  
  
(2 row(s) affected)  

C. Displaying all login mappings for a local login

The following example displays all locally defined login mappings for the login Mary.

EXEC sp_helplinkedsrvlogin NULL, 'Mary';  
GO  

Here is the result set.

Linked Server    Local Login   Is Self Mapping Remote Login   
---------------- ------------- --------------- --------------   
Sales            NULL          1               NULL  
Sales            Mary          0               sa  
  
(2 row(s) affected)  

Security Stored Procedures (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_droplinkedsrvlogin (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft