SQL server database server Link

Cause: There are many users and a large amount of data. Multiple databases occupy one database server. Linked Server is used to process distributed queries: when the client application executes distributed queries through the linked Server, SQL Server analyzes the commands and sends requests to OLE DB.

Sp_addmediaserver: register the remote server information on the local server.

Sp_add1_srvlogin: create or update the sp_ing between the login name on the local SQL Server instance and the security account in the remote server sp_addmediaserver [@ server =] 'server' // name of the connection Server to be created [, [@ srvproduct =] 'product _ name'] // product name of the OLEDB data source linked to the Server. The default value is NULL. If it is SQL Server, you do not need to specify it. [, [@ Provider =] 'provider _ name'] // unique programming identifier of the ole db access interface corresponding to this data source

[, [@ Datasrc =] 'data _ source'] // name of the data source interpreted by the OLEDB access interface

Sp_add1_srvlogin

[@ Rmtsrvname =] 'rmtsrvname' // name of the linked server mapped to application Logon

[, [@ Useself =] 'true' | 'false' | NULL] // determines whether to connect to rmtsrvname by simulating a local login name or explicitly submitting a login name and password. The default value is TRUE.

If the value is True, the login name uses its own creden to connect to the rmtsrvname, while ignoring the rmtuser and rmtpassword parameters;

The value of False indicates that the rmtuser and rmtpassword parameters are used to connect to the rmtsrvname of the specified locallogin. If the rmtuser and rmtpassword are also set to NULL, the login name or password is not used to connect to the server.

[, [@ Locallogin =] 'locallogin'] // logon on the local server. The default value is null. Null indicates that this option is applied to all local logins connected to the rmtsrvname. If it is not null, locallogin can be an SQL Server logon or Windows logon.

[, [@ Rmtuser =] 'rmtuser'] // The remote login name used to connect to the rmtsrvname when @ useself is FALSE. The default value is NULL.

[, [@ Rmtpassword =] 'rmtpassword'] // password associated with rmtuser. The default value is NULL.

 

Example:

Exec sp_addrole server 'pub', '', 'sqloledb', '2017. 168.1.38 'exec sp_addinto srvlogin 'pub', 'false', null, 'sa ', '000000' SELECT * FROM pub. ce_pub.dbo.t_pub_user