Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Customizing the Status Bar for each SQL Server SSMS Connection


By:   |   Read Comments (7)   |   Related Tips: More > SQL Server Management Studio

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

Almost every DBA has to manage more than one instance of SQL Server. If you are administering production, test and development servers then there are probably databases with similar names and structures in each of these instances. The scenario becomes more complicated when you are working with multiple logins in each instance at the same time. Chances increase to run a script on an instance other than on which it was intended to run. Although every query pane in SSMS includes information such as server instance and user this information is not always displayed prominently and it takes a bit of effort to note the details from tab to tab. In this tip, I will show an option in SSMS (SQL Server Management Studio) 2008 to identify instances and users using customized status bars.

Solution

Query panes in SSMS can be accessed several ways; from the file menu, the tool bar or by using Cntrl+N. In addition, from one desktop you can connect to production, test, development or any SQL Server as long as your have the right credentials. This tip will show you a way in which to identify a query pane by using customized color bars indicating the related server instance and login to which the query pane is linked.

Customize Query Pane Color for Current Connection

Create a new connection to one of your SQL Servers from SQL Server Management Studio.

database engine

When the connection window opens enter a user name and password and then click on the "Options >>" button on the bottom right.

A new tab named "Connection Properties" will become available.

  1. Click on the "Connection Properties" tab
  2. Check "Use custom color"
  3. Click the "Select..." button
  4. Select a color from the color chart
  5. Select "OK"
connection properties

Once you have selected a customized color, click "Connect" to connect to the server using this new color customization. This color customization will also be saved for this specific instance and user for future connections. If at this point you are not able to connect to the SQL Server for any reason the color customization will not be preserved.

Now open a new query pane for this connection and verify that the color of the status bar is the same that was entered in the connection properties. Whenever you connect to this server instance using this same user the status bar in the query pane will use this customized color. You can assign different colors to each login for a single instance.

Customize Query Pane for Registered Servers

Many administrators register their instances in SSMS. You can also customize the color for each registered instance.

For servers you already have registered right click on the server name in the Registered Servers list and select "Properties".

microsoft sql server management

For new servers go through the process of registering the server.

Now for both new or existing registered servers go to the "Connection Properties" tab and follow the steps above to customize the color.

edit server registration properties

Now any time you open a query pane using this registered server the same color will be applied to the status bar.

Something to notes is that you can only register an instance once, so this limits the color schemes that you can use for different logins.

What it Looks Like

So when you have selected a customized color, the color on the bottom status line will match the color you selected when you connected or whatever has been setup for your registered instance. As you can see below the status bar has been setup to red for this connection.

sql server management studio

Here are some additional points to be aware of when applying customization.

  • You can use SQL Server or Windows Authentication for color customization in SSMS.
  • Customization can be used for pre SQL Server 2008 instances when accessed using SSMS 2008.
  • Customization performed through registered servers and the server connection window work independent of each other, even for the same login.
  • Color customization is stored locally within SSMS, so the customizations do not show up if you use SSMS from another machine.
  • Customized color status bars are only available for database engine connections. Analysis services, reporting services and integration services do not have a customized color option for their connections.
Next Steps

As a part of best practices, you can implement customization of the status bar color for SSMS 2008

  • It is a better approach to register instances on your SSMS for your login and customize the color for the registered server. Click here to read about registering instances.
  • You can install and use SSMS 2008 for administration of pre 2008 instances of SQL Server
  • You should look to implement standard colors for your status bars. One scenario would be
    • red for production
    • green for test
    • blue for development


Last Update:


signup button

next tip button



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, July 23, 2015 - 4:26:53 AM - Ray Fox Back To Top

Thanks for this post - very useful.

I would like to now if there is a way of easily identifying open query windows. When more than 3 or 4 are open, and the files are not saved, it is trial and error to get to the query window you want because there is no preview. Maybe I am dreaming, but it would be nice to be able to identify the content of a query window without having to actually select it and realize it's the wrong one.

 


Wednesday, May 01, 2013 - 3:19:21 PM - shawn Back To Top

this is great for when you use queries however if you use views more than queries you get nothing.


Tuesday, July 24, 2012 - 8:10:23 AM - Andrei Back To Top

SSMSBoost add-in allows to "save" preferred connections and easily switch between them using own drop-down on toolbar. Each connection can be assigned connection color (in native way). SSMSBoost Tracks currently active connection and changes connection color whenever database or server changes. (this issue does not work clear in SSMS, say, when you run "use XXX" statements). SSMSBoost also allows to specify default connection color per server and "redefine" it for some special databases.

Scheme can be:

Test Server, all databases: Green

Test Server, RC database: Yellow or Orange

Production Server, All databases - RED

 


Thursday, November 05, 2009 - 2:08:58 AM - @tif Back To Top

 @cranfield: You have a good idea but I did not find any dynamic way for color customization. You have to allot color for each server seperately either through SSMS connection window or through any SSMS configuration file like that of RegSrvr.xml for registered servers.

Thanks


Tuesday, November 03, 2009 - 7:02:48 AM - @tif Back To Top

admin@ For my srevers i use same color mapping as mentioned. There may be other types required to include in some cases. e.g. Reporting servers or stand by servers. Also there may be more than one srevers of same category e.g. for production or development. I want to convey an idea that in single organisation color mappng may be standardized by DBAs. It may be documented. 

 Regards


Monday, November 02, 2009 - 10:31:51 AM - --cranfield Back To Top

can you configure SSMS to change colour depending on a substing of the server name e.g.:

all servers starting with P - red

all servers starting  T - green

 

etc 


Monday, November 02, 2009 - 9:22:31 AM - admin Back To Top

Atif,

Thank you for outlining this SSMS feature.  Is this typically the way you use it?

  • red for production
  • green for test
  • blue for development
  • Do you have any other use cases?

    Thank you,
    The MSSQLTips Team


    Learn more about SQL Server tools