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
This will update password for existing login .
If we want to map a login to other login on remote server
More details we can find on BOL for it.
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:
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?
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.
[nocontactorcommunitycontractcourtdocs']
User123: linkedserver "EXCEL" BANNED
Post a Comment