DECLARE @strLinkedServer NVARCHAR(100)

SELECT @strLinkedServer = 'Prod2005Server'

EXECUTE master.dbo.sp_dropserver
  @server = @strLinkedServer,
  @droplogins = 'droplogins'

EXECUTE master.dbo.sp_addlinkedserver 
  @strLinkedServer, 
  'SQL Server'

EXECUTE master.dbo.sp_addlinkedsrvlogin 
  @rmtsrvname = @strLinkedServer,
  @useself = N'False',
  @locallogin = N'SomeLocalUser', -- add local login
  @rmtuser = N'SomeDomain\SomeWindowsUser',  -- add the remote login
  @rmtpassword = '****' -- add the remote login password