For a long time there used to be a Connect item for this, but now I only get an error message when I try to access it, so it's time for a new one! By the way, this old item was for quite some the top-voted item in the old Ladybug system which predated Connect.
Today when you use ALTER TABLE ADD to add a column, a new column is always placed as the last column. This is far from often desireable. Often developers and database designers want to keep some logic in a column order, so that related column are close to each other. A standard rule we keep in the system I work with is to always have auditing columns at the end. Furthermore many graphical design tools encourage this kind of design, both bottom-end tools like the Table Designer in SSMS as well as high-end data-modelling tools such as Power Designer.
Today, if you want to maintain column order you have no choice but to go the long way: create a new version of the table and copy over. It takes time, and if not implemented correctly, things can go very wrong.
Requests about putting a column in a certain order is often met with objections that column order does not matter for performance, but this is not about database performance - it's about human performance. You want the database documentation to have the same order as the database, and the documentation is easier to read if order is logical.