Central Management Servers

Within SQL Server Management Studio you can define Registered Servers under Central Management Server and run a single query across multiple servers. This is very useful for monitoring.
CMS uses Windows Authentication only. Before registering servers, you need to ensure each server you register has the same windows user (login and password). You must create this user as a login within SSMS.

Permissions within SQL Server

Let's have the username 'ServerMaster'. We need no special permissions on the Windows Server, basic User group membership is fine. Within SSMS Servermaster needs VIEW SERVER STATE and VIEW DATABASE STATE to be granted to them. These are very basic rights. If you log in to SSMS using the ServerMaster login, you won’t be able to create or destroy anything.
Within SSMS on the server you setup your CMS, Servermaster is a db_owner of msdb, simply to allow the configuration of CMS. There is no need to add this permission on any other server.

a) Create logins:

1. Create Windows user for ‘ServerMaster'
2. Login to SSMS and execute (replacing ‘DOMAIN’):

USE [master]
GO

CREATE LOGIN [DOMAIN\ServerMaster]
FROM WINDOWS
GO

GRANT VIEW SERVER STATE TO [DOMAIN\ServerMaster];
GO
GRANT VIEW DATABASE STATE TO [DOMAIN\ServerMaster];
GO

b) Setup a Central Management Server

1. Open SSMS without logging in to any database. Select View then Registered Servers
2. Expand Database Engine, right-click Central Management Servers and select Register Central Management Server. Select your base server (eg local). It can be a SSMS instance created just for CMS.
3. Right-click your Central Management Server and select New Server Group (eg MyServers). You can have as many group as you like.
4. Right-click the Server Group and select New Server Registration. Do this to register each server using Windows Authentication
5. To query all servers, right-click the Server Group and select New Query.