sp_addlinkedsrvlogin - To modify or add new login on linked server

sp_addlinkedsrvlogin

To modify or add new login for linked server




We all know we are creating linked server run queries on remote server. Then using four part naming convention we can run query on remote servers.
As a DBA we have to periodically change password for logins on servers. If we change password for logins on remote serves we also have to update it on linked servers we have created on other servers for it.

sp_addlinkedsrvlogin can solve this purpose. It is used to add new login or modify existing login .

sp_addlinkedsrvlogin @rmtsrvname =
, @useself =
, @locallogin =
, @rmtuser =
, @rmtpassword =

Ex.
if we have changed password for a specified login on a remote server and we want to update its password on linked server

EXEC Sp_addlinkedsrvlogin
  @rmtsrvname ='192.168.0.51',
  @useself = 'FALSE', --specify false when using remote login and password
  @locallogin = NULL ,-- not used because we are not mapping remote and local login
  @rmtuser ='sa',
  @rmtpassword = 'test123'



This will update password for existing login .


If we want to map a login to other login on remote server

  EXEC Sp_addlinkedsrvlogin
  @rmtsrvname = 'test server',
  @useself = 'false',
  @locallogin = 'TestDomain\ABC',
  @rmtuser = 'MaryP',
  @rmtpassword = 'd89q3w4u'



More details we can find on BOL for it.

3 comments:

vannem said...

OLE DB provider "SQLNCLI" for linked server "MyLinkedServer" returned message "Deferred prepare could not be completed."

I am getting the above error when query the linked server database table.
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'db.myDatebase.Table1'.

MyQuery:

select *
from openquery(MyLinkedServer, 'select * from db.myDatebase.Table1'

Any idea?

Unknown said...

Hi, I´m new at SQL.
I have SQL Server 2005.
I made a Linked Server with:
EXEC sp_addlinkedserver 'EXCEL',
'Excel',
'Microsoft.ACE.OLEDB.12.0',
'C:\Documents and Settings\Eduardo\Mis documentos\NuevosPrecios.xlsx',
NULL,
'Excel 12.0 Xml';
GO

Is it OK?

Then, I´m trying to make a SP with:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE Proc [dbo].[spActualizarPreciosDesdeExcel]
As
Begin
UPDATE dbo.ARTICULOS_RUTAS
SET aruPrecio = Precio
FROM EXCEL...[Precios$]
JOIN dbo.ARTICULOS_RUTAS ON aruCodArt = CodigoArticulo
AND aruCodRut = CodigoRuta
End

but, when I press Execute I get:
Msg 7399, Level 16, State 1, Procedure spActualizarPreciosDesdeExcel, Line 13
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EXCEL" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Procedure spActualizarPreciosDesdeExcel, Line 13
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "EXCEL".

Help me please.

ANLIDAGOAT. said...

[nocontactorcommunitycontractcourtdocs']
User123: linkedserver "EXCEL" BANNED

Post a Comment

Popular Posts