Exporting and Sharing Registered Server Groups in SSMS

Registered server groups turn a messy list of server names into something your whole team can share. Build the list once, export it without passwords, and check every destination before you run anything.

Why a shared server list saves you

Picture 4:55 PM on a Friday. A colleague asks you to clean up some test rows. You open a new query window, pick a server from memory and run the DELETE. The server name looked right. It was the production twin with a similar name. Nobody enjoys that weekend.

A registered server list does not make you smarter, but it does take the guessing out. Production sits in one group. Test sits in another. Everyone on the team sees the same names. Here is how I set it up.

Create the groups and registrations

In SSMS, open View, then Registered Servers. Under Database Engine, right-click Local Server Groups and add a group. I use one group per environment, such as Production and Test. Then right-click a group and register each server in it.

In the registration dialog, set the server name, the authentication, and a default database. On the connection properties tab you can pick a custom color. I make production red. A red query window is a very good reminder.

Check the destination, not the label

A registered name is only a label that someone typed. The server you reach is what counts. After you connect, run this once. It shows the real server, the database and your login.

SELECT @@SERVERNAME AS server_name,
       DB_NAME() AS database_name,
       ORIGINAL_LOGIN() AS connected_login;

You can also make a script check itself. Put a test like this at the top of anything that changes data. It names the server you expect. The name below is made up, so on your server it says to stop. Put in your own test server name and it says safe to continue. In a real change script, turn the check into an IF with a THROW, so the batch stops.

DECLARE @ExpectedServer sysname = N'TESTSQL01';

SELECT CASE WHEN @@SERVERNAME = @ExpectedServer
            THEN N'Safe to continue'
            ELSE N'Wrong server, stop here'
       END AS status;

Export the group without passwords

Right-click the group, choose Tasks, then Export. SSMS saves a .regsrvr file. Before you click OK, tick the box named Do not include user names and passwords in the export file. Then the file carries server names and group layout, and no credentials.

Keep the file in a team folder, not in email threads with a dozen forwards. And do not skip that checkbox. A saved password in a shared file is a password you cannot take back.

Import it and run a read-only group query

Your colleague right-clicks Local Server Groups, chooses Tasks, then Import, and picks the file. They must re-enter authentication details on their own machine. That is the point. After importing, they should connect to each server once and run the identity query above.

Now the fun part. Right-click a group and choose New Query. SSMS runs your statement against every server in it, and the results come back merged. SSMS can add a column with the server name. If you do not see it, look at the multiserver results options under Tools, Options, Query Results.

SELECT CONVERT(nvarchar(128), SERVERPROPERTY('ServerName')) AS server_name,
       name AS database_name,
       state_desc,
       compatibility_level
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

You get one row per user database, with its state and compatibility level, for each server in the group. It is a fast way to spot the one server left on an old level.

Keep group queries read-only

A group query multiplies your reach. One typo now hits ten servers instead of one. So I keep casual group queries to SELECT only. For a change, use a smaller group and check the target list first. Same database name on two servers does not mean the same application data.

Finally, give the list an owner. Remove servers that have been retired, add new ones, and reshare the file. A list nobody maintains slowly turns into the old problem again.

Take ten minutes this week and group your servers by environment.

A shared server list is not a permission slip, it is a map that still needs checking.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.