在兩台伺服器之間建立視圖

I have two MS SQL Servers 2000 on two machines. Is there a way I can create a view table in ServerA, which would look at ServerB, using a ServerA user, who does not exists in ServerB.

First Link the ServerB to ServerA:

EXEC sp_addlinkedserver 'ServerB', 'SQL Server'

Then establish the user that we'll use to connect from ServerA to ServerB.

For that task we'll use a valid account in ServerA (with the appropriate permissions) and map it to a valid account in ServerB. That account must have permission to access the source table for the view in ServerA:

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'ServerB',

@useself = 'false',

@locallogin = 'YourLocalUserFromA',

@rmtuser = 'YourRemoteUserFromB',

@rmtpassword = 'PasswordOf_rmtuser'

Log in to ServerA with the account that we mapped to ServerB and create the view:

CREATE VIEW dbo.MyView

AS

SELECT Field1, Field2, FieldN

FROM ServerB.RemoteDB.dbo.RemoteTable

GO

Start using it, and good luck:

SELECT * FROM MyView