It's very simple use Enterprise Manager -> Tables _> Design Table -> and use drag and drop to move one column in the position you want (it' necessary to select the row of the column in order for drag and drop to function)
Beware, this only can be done in SQL Server 2k not in any previous versions of SQL Server.
Anyway to move a column has a meaning only for a better visualisation of the table design, because the order of columns, or the phisical order of rows in the table has no importance (it's a base rule of relational database).
But, if you decide to move one column before another, you can do it nothing is going to change within your application.
In fact, there is one problem in SQL 2k. If you are changing the position of a column and for that table you have a trriger that use the function COLUMNS_UPDATED(n) (where n is the no of a row in the table) and after your switching positions of columns the positin of the column implied in COLUMN_UPDATED function changes you should modify your trriger in order to work corectly.