Linked Server Login Add and remove

One, create linked server login via Sys.sp_addlinkedsrvlogin

When you need to access linked server on the local server, local server must logon to linked server, use Sys.sp_addlinkedsrvlogin to specify local server Login login account for linked server. If linked server is SQL Server, you must use SQL Server authentication and you cannot log on using Windows Authentication domain accounts.

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

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote Server .

sp_addlinkedsrvlogin[@rmtsrvname =]‘Rmtsrvname‘[, [@useself =]‘TRUE‘|‘FALSE‘| null[, [@locallogin =   ' [, [@rmtuser =   ' [, [@rmtpassword =            

Arguments

[@rmtsrvname = ] ' rmtsrvname '

is the name of a linked server, 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 Passwor D. The data type is varchar (8) and with a default of TRUE.

A value of TRUE specifies-logins use their own credentials -connect to Rmtsrvname, with the Rmtuser and rmtpassword arguments being ignored. FALSE Specifies that the rmtuser and rmtpassword arguments is used to connect to rmtsrvname for the specified LocalLogin. If rmtuser and Rmtpassword are also set to NULL, no login or password was used to connect to the linked server.

[@locallogin = ] ' locallogin '

is a login to the local server. locallogin is sysname, with a default of NULL. NULL Specifies the This entry applies to all local logins the connect to rmtsrvname. If not NULL, the locallogin can be a SQL Server login or a Windows login. The Windows login must has been granted access to SQL Server either directly, or through it membership in a Windows Grou P granted access.

[@rmtuser = ] ' rmtuser '

Is the remote login used to connect to rmtsrvname when @useself is FALSE. When the remote server was an instance of SQL Server this does not with 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.

Remarks

A default mapping between all logins on the local server and remote logins on the linked server is automatically created B Y executing sp_addlinkedserver. The default mapping states that SQL Server uses the user credentials of the local login when connecting to the linked serv Er on behalf of the login. This is equivalent to executing sp_addlinkedsrvlogin with @useself set to True for the linked server, without SPECIFYI ng a local user name. Use sp_addlinkedsrvlogin-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 have to use sp_addlinkedsrvlogin to create a predetermined login mapping, SQL Server can automatically use th E 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 was available on the client and sending server.

• The provider supports Windows authentication Mode; For example, SQL Server running on Windows.

After the authentication have been performed by the linked server by using the mappings that is defined by executing SP_AD Dlinkedsrvlogin on the local instance of SQL Server, the permissions on individual objects in the remote database is Dete Rmined by the linked server and not the local server.

Two, Examples to add linked server login

1, connecting all local logins to the linked server by using their own user credentials

Using the user's own credentials to log on to linked server requires that each user login be explicitly mapping to the linked server so that it has permission to log on.

'Accounts'true';   

If there is explicit mappings created for individual logins, they take precedence over any global mappings so may exist For that linked server.

2, connecting a specific login to the linked server by using different user credentials

The following example creates a mapping to make sure so the Windows user Domain\mary connects through to the linked Server Accounts by using the login maryp and password d89q3w4u.

'Accounts'false'domain\mary'maryp'd89q3w4u ';     

Mapping Windows Domain Account "Domain\mary" to the account "MARYP" of SQL Server authentication, giving it permission to log on to linked server.

3, the @locallogin parameter is null,null specifies that this entry applies to all local logins the connect to rmtsrvname. Make all Local Login can use "MARYP" to log on to linked Server.

'Accounts'false', null'maryp'd89q3w4u';       

Third, view the added linked Server Login via Sys.linked_logins

Returns a row per Linked-server-login mapping, for use by RPC and distributed queries from local server to the Correspondi ng linked server.

server_id: ID of the server in Sys.servers. 0 represents the local server, and an integer value greater than 0 represents the ID of the linked server.

local_principal_id:Server-principal to whom mapping applies.Iflocal_principal_idNot 0, whose value is the server Principal ID for the parameter @locallogin, using sys.server_principals to view the server Principal that the ID corresponds to;local_principal_id=0, indicating @locallogin =null.

uses_self_credential: If 1, mapping indicates session should use its own credentials; Otherwise, 0 indicates that session uses the name and password that is supplied. The values and parameters @useself the same

remote_name: Remote user name to use when connecting. Password is also stored and not exposed in catalog view interfaces. When linking to linked server, use Remote_name to the linked server, which is login to linked server.

Four, delete linked Server Login via Sys.sp_droplinkedsrvlogin

Removes an existing mapping between a login to the local server running SQL server and a login on the linked server.

['rmtsrvname'['locallogin'      

[ @rmtsrvname = ] ' rmtsrvname'

is the name of a linked server, the SQL Server login mapping applies to. rmtsrvname is sysname, with no default. rmtsrvname must already exist.

[ @locallogin = ] ' locallogin'

is the SQL Server login on the local server, which has a mapping to the linked Server rmtsrvname. locallogin is sysname, with no default. A mapping for locallogin to rmtsrvname must already exist. If NULL, the default mapping created by sp_addlinkedserver, which maps all logins on the local server to logins O n the linked server, is deleted.

View registered linked logins through sys.linked_logins , get local_principal_id data and server_id, sys.servers View the name of the linked Server and view the registered local login via sys.server_principals .

Example: Removing the login mapping for an existing user

The following example removes the mapping for the login Mary from the local server to the linked server Accounts. Therefore, login Mary uses the default login mapping.

'Accounts'Mary'   

Reference Documentation:

MSDN:SP_ADDLINKEDSRVLOGIN (Transact-SQL)

Linked Server Login Add and remove