Sp_Addlinkserver - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sp_Addlinkserver

Author

Topic

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-04-11 : 06:11:46

Hi

I have linked a server to my Server using the Sp_addlinkserver.
I log in with a different username and password to the linked server.

I can't query the linked server because of the different usernames and passwords (between the primary and linked).

Is there a way that I can query the linked server? How can I send the required username & password?

You can't teach an old mouse new clicks.

Richard Branson
Yak Posting Veteran

84 Posts

Posted - 2005-04-12 : 02:24:44

I found the solution

sp_addlinkedsrvlogin
Creates or updates a mapping between logins on the local instance of Microsoft® SQL Server™ and remote logins on the linked server.

Syntax
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @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 =] 'useself'

Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE. A value of true specifies that SQL Server authenticated 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. true for useself is invalid for a Windows NT authenticated login unless the Microsoft Windows NT® environment supports security account delegation and the provider supports Windows Authentication (in which case creating a mapping with a value of true is no longer required but still valid).

[@locallogin =] 'locallogin'

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 NT user. The Windows NT user must have been granted access to SQL Server either directly, or through its membership in a Windows NT group granted access.

[@rmtuser =] 'rmtuser'

Is the username used to connect to rmtsrvname when useself is false. rmtuser is sysname, with a default of NULL.

[@rmtpassword =] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

Examples
A. Connect all local logins to the linked server using their own user credentials
This example creates a mapping to ensure that all logins to the local server connect through to the linked server Accounts using their own user credentials.

EXEC sp_addlinkedsrvlogin 'Accounts'

Or

EXEC sp_addlinkedsrvlogin 'Accounts', 'true'

B. Connect all local logins to the linked server using a specified user and password
This example creates a mapping to ensure that all logins to the local server connect through to the linked server Accounts using the same login SQLUser and password Password.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', NULL, 'SQLUser', 'Password'

C. Connects a specific login to the linked server using different user credentials
This example creates a mapping to ensure that only the Windows NT user Domain\Mary connects through to the linked server Accounts using the login MaryP and password NewPassword.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'NewPassword'

D. Connects a specific login to an Excel spreadsheet (the linked server)
This example first creates a linked server named ExcelSource, defined as the Microsoft Excel spreadsheet DistExcl.xls, and then creates a mapping to allow the SQL Server login sa to connect through to ExcelSource using the Excel login Admin and no password.

EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyData\DistExcl.xls',
NULL,
'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL

You can't teach an old mouse new clicks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources