Include headers when copying results from SQL Management Studio

A handy little option to enable in Management Studio for users who copy and paste query results from the grid into Excel or other applications, is to include the headers as well as the data.

This can be done from the "Tools" -> "Options..." window.

Expand Query Results, then SQL Server, and finally select Results to Grid.

On the right you should now see the option to "Include column headers when copying or saving the results" - enable this and no longer will you have to type the headers into the spreadsheet again!

SQL 2008 Management Studio shows filename on tab

Another frequent complaint from users moving to the SQL 2008 Management Studio is that the text on the tabs now shows the filename rather than the server and database.

Don't panic!  This can be changed back!!

Simply select the Tools menu, and select "Options..."

Now in the left pane expand the "Text Editor" and select "Editor Tab and Status Bar". 

Finally scroll the right pane down to the bottom so you can see the "Tab Text" heading, and under that you can select what you want included on the tabs.

Cannot change table in SQL 2008 Management Studio

As more users are upgrading to SQL 2008, they are running into some of the small changes in the 2008 management studio.

One of these is an error message that pops up when a user tries to change a table via management studio design view by changing/deleting a column or adding a new column in the middle of the table.  Instead of saving the change, the user gets the following error:

This may fool you or the user into believing there is a permissions issue preventing the table being updated, but it's actually a setting in SSMS - presumably to prevent the application hanging if the table involved was of any significant size.

The fix though is to simply uncheck box in the Options window. 

Select "Tools" from the SSMS menu bar, then "Options..." from the drop down list. 

Expand the "Designers" section, and under the "Table and Database Designers" section, you should find the "Prevent saving changes that require table re-creation" option, which you can disable.

Or you could just learn how to use ALTER TABLE statements in T-SQL!

Fix orphaned SQL user logins on new server

This piece of code helps in the situation where you have attached or restored a database onto a different server or instance from where it originated.

SQL will automatically re-apply permissions for any windows users as the sid is unique across the domain, but for any SQL users you will find that even if the same names exist on the new server, they will not have the required access.

To fix this, you need to use sp_change_users_login with @Action of Auto_fix - this will remap the user sid in the database, to the login sid for this SQL instance.