IF OBJECT_ID('sp_RmtLogin','P') IS NOT NULL
DROP PROC dbo.sp_RmtLogin
GO
/***************************************************************************
* Author: Roy
* Date: 04/14/2010
* Test: exec sp_RmtLogin xxxx, 'uid','pwd', 'Sql207'
***************************************************************************/
CREATE PROC dbo.sp_RmtLogin
(
@rmtSrvName varchar(20),
@rmtSrvUid varchar(20),
@rmtSrvPwd varchar(20),
@rmtSrvAlias varchar(20)
)
AS
BEGIN
IF exists (select * from sys.servers where name = @rmtSrvAlias )
BEGIN
EXEC sp_dropserver @rmtSrvAlias,'droplogins'
END
DECLARE @rmtConn_str VARCHAR(200)
SET @rmtConn_str = 'SERVER=' + @rmtSrvName
EXEC sp_addlinkedserver
@rmtSrvAlias, --Remote server alias name in the local mapping table
'', --Product name
'SQLOLEDB', --Provider name
NULL, --Data source name
NULL, --location
@rmtConn_str, --Connection string to the remote database server
NULL --Catelog
EXEC sp_addlinkedsrvlogin
@rmtSrvAlias, --Remote server alias in local mapping table
'false', --Indicates the authentication type is SQL Server Authentication, so need username and password.
NULL, --The local login name, if null means for all
@rmtSrvUid, --Username used to connect the remote server.
@rmtSrvPwd --Password used to connect the remote server.
END
GO
Select * from information_schema.routines
select * from sys.servers
select * from information_schema.tables
select * from information_schema.columns
