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.

4 comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts